Forum Discussion
Change in Values over Time - Calculated Column?
- 8 years ago
- 7 years ago
Okay, for anyone that comes across this thread in the future...
Removal of the 'index' column was a bad idea.
While it works without it, using the Date, as an index, memory consumption jumps through the roof. So much so that with the addition of a couple more weeks of data my PC couldn't refresh. And then, with a couple more weeks, the Power BI Service couldn't refresh.
Here is the DAX with the Index column:
QtyAvailableChange = IF ( ISBLANK ( LOOKUPVALUE ( Data[QtyAvailable], Data[Date], CALCULATE ( MAX ( Data[Date] ), FILTER ( Data, Data[Index] < EARLIER ( Data[Index] ) && Data[SKU] = EARLIER ( Data[SKU] ) && Data[Location] = EARLIER ( Data[Location] ) ) ), Data[SKU], Data[SKU], Data[Location], Data[Location] ) ), 0, [QtyAvailable] - LOOKUPVALUE ( Data[QtyAvailable], Data[Date], CALCULATE ( MAX ( Data[Date] ), FILTER ( Data, Data[Index] < EARLIER ( Data[Index] ) && Data[SKU] = EARLIER ( Data[SKU] ) && Data[Location] = EARLIER ( Data[Location] ) ) ), Data[SKU], Data[SKU], Data[Location], Data[Location] ) )Do it this way - not the way I previously referenced.
To create the index column I added Index as a custom column in Query Editor with the M query:
Index = Duration.Days(Date.From([Date])-#date(YYYY,MM,DD))
Where YYYY,MM,DD is the Year, Month and Day of my earliest Date record.
I have 2 input tables shown below
CFY refers to Current FinYear and NFY refers to Next Finyear
Total Income
| Company | OctCFY | NovCfy | DecCFY | JanNfy | Febnfy | Marnfy | Aprnfy | maynfy | junnfy | Julnfy | Augnfy | Sepnfy | Octnfy | Novnfy | Decnfy |
| A | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
| B | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
| C | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
| D | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
| E | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
| F | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
Admin Cost
| Company | OctCFY | NovCfy | DecCFY | JanNfy | Febnfy | Marnfy | Aprnfy | maynfy | junnfy | Julnfy | Augnfy | Sepnfy | Octnfy | Novnfy | Decnfy |
| A | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 |
| B | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 |
| C | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 |
| D | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 |
| E | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 |
| F | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 |
I am showing months from current month + remaining months of the year + next year all the months. This will change dynamically.
Output Table
VAT
| Company | Jancfy | Febcfy | Marcfy | Aprcfy | maycfy | juncfy | Julcfy | Augcfy | Sepcfy | Octcfy | Novcfy | Deccfy | Jannfy | Febnfy | Marnfy | Aprnfy | maynfy | junnfy | Julnfy | Augnfy | Sepnfy | Octnfy | Novnfy | Decnfy |
| A | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 10 | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 0 |
| E | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 10 | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 0 |
| D | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 10 | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 0 | 0 | 22.5 | 0 |
This calculations has to be done for every second month of the quarter. ie) Feb, May, Aug, Nov. and It has to be done only when month is January. February value will be a manual input and the rest of all the months needs a calculation.
When month is jan, Report shows value from current year jan to next year december.
May = sum(total income of Jan+ feb+ mar)*25% + sum(admin cost of Jan + feb+ mar)*25%
Aug =sum(total income of Apr+ May+ Jun)*25% + sum(admin cost of Apr+ May+ Jun)*25%
Nov =sum(total income of Jul+ Aug+ Sep)*25% + sum(admin cost of Jul+ Aug+ Sep)*25%
Febnextyear= sum(total income of oct+ Nov+ Dec)*25% + sum(admin cost of oct+ Nov+ Dec)*25%
Maynextyear = sum(total income of Jannextyear+ febnextyear+ marnextyear)*25% + sum(admin cost of Jannextyear + febnextyear+ marnextyear)*25%
Augnextyear=sum(total income of Aprnextyear+ Maynextyear+ Junnextyear)*25% + sum(admin cost of Aprnextyear+ Maynextyear+ Junnextyear)*25%
Novnextyear =sum(total income of Julnextyear+ Augnextyear+ Sepnextyear)*25% + sum(admin cost of Julnextyear+ Augnextyear+ Sepnextyear)*25%
Here is the calculated measure which i got,