DAX - Cumulative total without date-formatted source data-CodePudding

I am trying to implement a YtD measure for my report in Excel with Power Pivot. My source looks roughly like this:

Table 1


| Month    | Store | Branch | Article | Value |
|----------|-------|--------|---------|-------|
| January  | 1     | A      | Sales   | 200   |
| January  | 1     | A      | Costs   | 100   |
| January  | 1     | A      | Rent    | 10    |
| February | 1     | A      | Costs   | 20    |
| February | 1     | A      | Sales   | 80    |
| March    | 1     | A      | Costs   | 30    |
| March    | 1     | A      | Sales   | 80    |
| February | 2     | B      | Sales   | 100   |
| February | 2     | B      | Costs   | 40    |
| February | 2     | B      | Rent    | 20    |

Linked to it, are a table Table 2 of months (name - number from 1 to 12), a table Table 3 of unique articles and a table Table 4 of unique stores with their branches.

I want to be able to display YtD for every article depending on the chosen month.

I have measures:

Val. := sum(table1[Value])
YtD1:= calculate(Val., all('Table 2'[Name]))

The former sums across all the values, which are filtered by article in my pivot report. The latter calculates a YtD across all months. It works, but I have to rewrite it so that it responds to filtering the last month and sums from the first month to the selected month.

I have tried to format month numbers to process them as dates (e.g. first day of the month), but couldn't appropriately handle the FORMAT function.

I have also tried to do a sum of months, i.e.:

YtD2= calculate(Val., filter(Table2;Table2[Number]<=2))

which, I hoped, would count months from January to February. It doesn't seem to do any good, resulting in numbers I cannot explain.

My desired output should look like this:

| Store | Sales |     | Costs |     |
|-------|-------|-----|-------|-----|
|       | Val.  | YtD | Val.  | YtD |
| 1     | 80    | 280 | 20    | 120 |
| 2     | 100   | 100 | 40    | 40  |

if data is filtered by February.

| Store | Sales |     | Costs |     |
|-------|-------|-----|-------|-----|
|       | Val.  | YtD | Val.  | YtD |
| 1     | 160   | 360 | 50    | 150 |
| 2     | 100   | 100 | 40    | 40  |

if February and March are selected (Val. is displayed for February and March, but YtD from January to March).

Is there a way to implement this in DAX? Can this be done without conversion from month names (or numbers) to some date&

If not, can I get it to work for a month filter instead of a month slicer? That is, if only one month can be selected.

I cannot use variables and similar Power BI features.

CodePudding user response：

Try:

YTD :=
VAR MaxSelectedMonth =
    MAX( Table2[Number] )
RETURN
    CALCULATE(
        [Val.],
        FILTER(
            ALL( Table2 ),
            Table2[Number] <= MaxSelectedMonth
        )
    )