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