DAX help

[mberndt]

Hi guys,

 

I would like to ask you to help me out with this problem.

I would like to calculate a value in period t and then i would like to substract value in period t-1 from it. (value [t] - value [t-1]). 

 

Fallout_count_t-1 =
CALCULATE (
    SUM ( NC_FALLOUT_TREND_DATA[FALLOUT_COUNT] ),
    FILTER ( 'NC_FALLOUT_TREND_DATA', MAX ( [ITERATION] ) )
)

- 

CALCULATE (
        SUM ( NC_FALLOUT_TREND_DATA[FALLOUT_COUNT] ),
        FILTER ( 'NC_FALLOUT_TREND_DATA', MAX ( [ITERATION] ) - 1 )
    )

 

Iteration is a whole number and I don't have a date in the table. I can't calculate t-1 value but it works if I just calculate MAX([ITERATION]) and MAX([ITERATION])-1 separately. So for MAX([ITERATION]) and MAX([ITERATION])-1 I get the right number, but it doesn't work in the calculate function. It brings 0 as a result for the substraction, because it calculates only the MAX ITERATION value somehow and not MAX([ITERATION])-1 in the second part of the substraction.

 

Thank you in advance for your replies!

[amitchandak · Accepted solution]

@mberndt , for all such case you you should have seperate table ITERATION or period and Try like

Assumsing  [ITERATION]  is column not measure

CALCULATE (
        SUM ( NC_FALLOUT_TREND_DATA[FALLOUT_COUNT] ),
        FILTER ( 'ITERATION',  [ITERATION]  = MAX ( [ITERATION] )  )
    )

CALCULATE (
        SUM ( NC_FALLOUT_TREND_DATA[FALLOUT_COUNT] ),
        FILTER (all( 'ITERATION'),  [ITERATION]  = MAX ( [ITERATION] ) - 1 )
    )

similar approach what I use for the week

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[mberndt]

@amitchandak Thank you for your reply!

I created a dimension table for Iteration as you mentioned it with Enter data and made the relationship between the two tables. I changed the meassure exactly how you wrote it but still have a problem. Currently it returns the fallout count value of the latest (== max iteration) iteration. So as the result of the substraction I get only the value [t] and not value [t] - value [t-1].

Can you please help? Thank you in advance!

[amitchandak]

@mberndt , hope for t-1 you are using all in filter like -all( 'ITERATION')

Because if "t" is selected, then you will not get t-1

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[mberndt]

@amitchandak I am sorry, it works well right now... Thank you for your quick replies, really appreciated!