Forum Discussion

martaTr's avatar
martaTr
Frequent Visitor
2 years ago

Week over Week difference

hi all, probably this is super easy, but i struggle with it, i need to present on below matrix table difference week over week just for the last two periods (in below between 20 and 27 march), so it basically should show me -22 etc. i did it in excel but we moved to power bi. this is a count of lines. i also have the same with sum of import. 

 

 

could you please help me how to do it? i tried with calculate and max or with dates in period. but it shows me strange results.

 

DAX

2 Replies

  • adudani's avatar
    adudani
    Memorable Member
    2 years ago

    hi martaTr ,

     

    if you have a weeknum column in your dataset, that maybe used. ( for more context: How to Calculate Week over Week Change in Power BI (youtube.com)

     

    OR,

     

    you could explore if the (52) Calculate Growth From Previous Non Consecutive Date - YouTube may be a better fit.

     

    Another alternative maybe to explore DATEADD function (DAX) - DAX | Microsoft Learn

     

    Kindly provide a sample input table masking sensitive data if this doesn't work for you.

    • martaTr's avatar
      martaTr
      Frequent Visitor
      2 years ago

      thank you, it seems like it's working, however i am wondering how can i this new metric WoW add to the table. 

      Here is my formula:

       

      Measure3 = 
VAR LastDate1 =
    MAXX ( ALLSELECTED ( Affinity[As of Date] ), Affinity[As of Date] )
VAR LastSales =
    CALCULATE (
        COUNT ( [Importo] ),
        FILTER ( ALL ( Affinity[As of Date] ), Affinity[As of Date] = LastDate1 )
    )
VAR PrevDate =
    MAXX (
        FILTER ( ALL ( Affinity[As of Date] ), 'Affinity'[As of Date] < LastDate1 ),
        Affinity[As of Date]
    )
VAR PrevSales =
    CALCULATE (
        COUNT ( [Importo] ),
        FILTER ( ALL ( Affinity[As of Date] ), Affinity[As of Date] = PrevDate )
    )
RETURN
    IF ( ISBLANK ( PrevDate ), BLANK (), LastSales - PrevSales )

       

      Unfortunately it doesnt work when i normally add it to columns/ rows/values.Now it's in seprate table hidden under a main one which doesnt look good and it 's completely impractical. Any ideas how to present that metric? maybe other chart?