Forum Discussion

mberndt's avatar
mberndt
Regular Visitor
5 years ago
Solved

DAX help

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!
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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-Last-Week/ba-p/1051123

    • mberndt's avatar
      mberndt
      Regular Visitor
      5 years ago

      @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's avatar
        amitchandak
        Super User
        5 years ago

        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.