Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate difference between two dates in weeks

Hello experts! 

I hope you can help me with this (as I am quite new to Power BI):

I created a measure which shows TOTAL NEED QTY as per image

What I want is a calculation that will show me the biggest difference between 2 selected dates of the Total Need (both, positive and negative greater difference)

 

Example from the image above, the biggest negative difference from 1 week to the other is BEKANT and the biggest positive difference is BILLY

RANGENeed Week 30Need Week 31Difference
BEKANT527879524333-3546
BILLY8328878349092022
EKEDALEN5056950798229
GALANT158730158852122
NEXUS000
REPVÅG190768191262494
STOCKHOLM4022740534307

 

Is this possible lads?
Thanks a lot in advance,

Regards,

Alan.

Need Help

2 Replies

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    Hi Anonymous ,

    Could you please share the expected result to us for analysis? Besides, you can share the original data to us for analysis.

    Best Regards,

    Teige

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello TeigeGao,

      Basically what I need is the following:

      YEAR WEEKNEEDNEED DIF PCSNEED DIF %
      2019 26955,589  
      2019 27993,32237,7334%
      2019 28910,235-83,087-8%
      2019 29836,998-73,237-8%
      2019 30846,7939,7951%
      2019 31833,641-13,152-2%
      2019 32636,781-196,860-24%
      2019 33615,317-21,464-3%

       

      I need to calculate the difference in pieces and % from one week to another but also to be able to do it even if comparing NON consecutive weeks (for instance, comparing week 26 to 30 and 33):

      YEAR WEEKNEEDNEED DIF PCSNEED DIF %
      2019 26955,589  
      2019 30846,793-108,796-11%
      2019 33615,317-231,476-27%

       

      • Fact table is 'SPI_NEED':
        • Dates on this table are under a column named [SPI_DATE] and it goes week by week (eg.: 01/01/19; 08/01/19; 15/01/19 and so on)
        • I created a measure named NEED TOTAL = SUM(SPI_NEED[NEED])
      • Table containing full date ranges is 'CALENDAR' and column with dates is [DATE] running from 01/01/19 till 31/12/19

      I don't know if this is enough for you or not otherwise let me know so I can help you to help me out :). This is my very first post so I am not fully aware of the "ways of working". Apologies for that in advance.

       

      Anyhow, thanks a lot for your time, much appreciated.

      Cheers.
      Alan.