YTD from a measure or another depending on having data or not

[Joao_Arienti]

Hi everyone,

I need your help on how to calculate YTD from two measures depending if one of them has data or not. And if not, it should be replaced by the other one.

At first, it seemed easy, but I'm having a hard time solving it. The concept is Measure BBB should be used and when Measure BBB has zero as value, it should be replaced by Measure AAA monthly inside a YTD calculation.

I have a dim measure table like this:

Measure SK	Measure Name
1	AAA
2	BBB
3	CCC
4	DDD
5	EEE

And a fact table like this:

Year Month	Date SK	Product SK	Store SK	Measure SK	Measure Value
202001	20200101	111	111	1	0
202001	20200101	111	111	2	10
202001	20200101	111	111	3	5
202002	20200201	111	111	1	100
202002	20200201	111	111	2	0
202002	20200201	111	111	3	5
202003	20200301	111	111	1	1000
202003	20200301	111	111	2	2000
202003	20200301	111	111	3	5

And I'd like the result to be something like this:

Year Month	Product SK	Store SK	Measure SK AAA	Measure SK BBB	Measure SK CCC	Output	YTD Output
202001	111	111	0	10	5	10	10
202002	111	111	100	0	5	100	110
202003	111	111	1000	2000	5	2000	2110

I leave here a link to a PowerBI example file with the data.

Link to PowerBI file 

Using DAX Studio, I tried a lot of different things, but I keep getting it wrong:

DEFINE
    MEASURE 'Fact_Table'[test_measure] =
        VAR _m1 =
            CALCULATE (
                SUM ( 'Fact_Table'[Measure Value] ),
                'Dim_Measure'[Measure Name] = "BBB"
            )
        VAR _m2 =
            CALCULATE (
                SUM ( 'Fact_Table'[Measure Value] ),
                'Dim_Measure'[Measure Name] = "AAA"
            )
        RETURN
            IF ( _m1 = 0, _m2, _m1 )
    MEASURE 'Fact_Table'[_m1] =
        CALCULATE (
            SUM ( 'Fact_Table'[Measure Value] ),
            'Dim_Measure'[Measure Name] = "BBB"
        )
    MEASURE 'Fact_Table'[_m2] =
        CALCULATE (
            SUM ( 'Fact_Table'[Measure Value] ),
            'Dim_Measure'[Measure Name] = "AAA"
        )
    VAR _y =
        TREATAS ( { "2020" }, 'Date'[Year] )
    VAR _mn =
        TREATAS ( { "AAA", "BBB" }, 'Dim_Measure'[Measure Name] )

EVALUATE
    SUMMARIZECOLUMNS (
        'Date'[Year Month],
        _y,
        "m1", 'Fact_Table'[_m1],
        "m2", 'Fact_Table'[_m2],
        "new_test", 'Fact_Table'[test_measure],
        "new_test_ytd", TOTALYTD ( 'Fact_Table'[new_test], 'Date'[Date] ),
        "new_test_ytd_new", CALCULATE ( 'Fact_Table'[new_test], DATESYTD ( 'Date'[Date] ) )
    )
    ORDER BY 'Date'[Year Month]

If anyone could help me, I'd be very grateful.

[v-yiruan-msft · Accepted solution]

Hello @Joao_Arienti ,

Update the [Output] measurement formula as follows:

Output = SUMX(VALUES('Fact_Table'[Year Month]),IF( [Total Measure BBB] = 0, [Total Measure AAA], [Total Measure BBB] ) )​

Best regards

Rena

[Joao_Arienti]

Thank you very much, Rena. Your suggestion seems to do the trick.

[amitchandak]

@Joao_Arienti , I have not checked the power bi file yet.

The first thing I noticed, this should

IF ( coalesce(_m1,0) = 0, _m2, _m1 )

or

IF ( isblank(_m1,0), _m1 , _m2)

[lbendlin]

Since you want your measure BBB to replace the measure AAA on a monthly basis you need to iterate through all "rows"  in your filter context. 

I would use a SELECTCOLUMNS() statement to create a table variable with the month name and the AAA and BBB measure for that month.  In the next step I would then use ADDCOLUMNS to decide which of the measures to use, and as a last step I would then add the YTD computation for each row of the temp table.