Forum Discussion
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...
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
Hello TeigeGao,
Basically what I need is the following:
| YEAR WEEK | NEED | NEED DIF PCS | NEED DIF % |
| 2019 26 | 955,589 | ||
| 2019 27 | 993,322 | 37,733 | 4% |
| 2019 28 | 910,235 | -83,087 | -8% |
| 2019 29 | 836,998 | -73,237 | -8% |
| 2019 30 | 846,793 | 9,795 | 1% |
| 2019 31 | 833,641 | -13,152 | -2% |
| 2019 32 | 636,781 | -196,860 | -24% |
| 2019 33 | 615,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 WEEK | NEED | NEED DIF PCS | NEED DIF % |
| 2019 26 | 955,589 | ||
| 2019 30 | 846,793 | -108,796 | -11% |
| 2019 33 | 615,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.