Forum Discussion
Change in Values over Time - Calculated Column?
I'll start out by saying I'm pretty new to Power BI and have clearly gotten in over my head with what I thought would be a relatively easy first 'real' use case...
This is a similar question to this post from last May.
My use case is a bit different, however, because of my data. The question, above has a single instance of the product per day where I have multiple (because of multiple locations). Additionally, I have multiple quantity columns (called 'values' in the question, above).
My data, basically, looks like this:
I want to create a calculated column for each of my Quantity columns so that the table would look like this (although the blank cells at the top should really be zero, not blank - but that's easy enough to fix using 'change value', I believe):
The end result would be a vizualisation that would look something like this:
When I tried to use the calculated column from that post the change is calculated against the previous instance of the QuantityAvailable (for example) and not the matching value based on location. I've spent a fair amount of time trying to modify the filter to make it work but I'm not having any luck so far.
Does anyone have any suggestions or assistance? Is there an easier way? A trick with an existing vizualization I'm missing?
Thanks,
James
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.
9 Replies
The key to this is to use the EARLIER function. You just need an extra FILTER statement that it needs to also match your "EARLIER" location as well.
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
The solution I was trying does use the EARLIER function:
ChangeAvailable = Inventory[QtyAvailable] - CALCULATE ( MAX ( Inventory[QtyAvailable] ), FILTER ( ALL ( Inventory ), Inventory[Index] = EARLIER ( Inventory[Index] ) - 1 && Inventory[SKU] = EARLIER ( Inventory[SKU] ) ) )And I understand that I need to modify the filter. One of the problems I ran into was that when I did so I ran into a circular-reference issue with the second calculated column. I feel like I'm close, I'm just missing something.
I'm gong to be spending some time this evening reading up on DAX and these functions - your article will be one of those resources.
Thank you,
James
Thank you very much, Ashish Mathur! This was very helpful and informative.
I did make a couple changes to the calculation. The sign of the number seemed backwards to me so I changed the order of the calculation. I also removed the dependance on the index column.
Here is the modified DAX:
QtyAvailableChange =
IF (
ISBLANK (
LOOKUPVALUE (
Data[QtyAvailable],
Data[Date], CALCULATE (
MAX ( Data[Date] ),
FILTER (
Data,
Data[Date] < EARLIER ( Data[Date] )
&& 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[Date] < EARLIER ( Data[Date] )
&& Data[SKU] = EARLIER ( Data[SKU] )
&& Data[Location] = EARLIER ( Data[Location] )
)
),
Data[SKU], Data[SKU],
Data[Location], Data[Location]
)
)I've experiemented with a vastly expanded dataset and I don't see any issues.
Thank you again for your help!
Edited: Changed DAX formatting and added clarification on the additional testing.
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,
VAR a =GENERATESERIES ( 5, 23, 3 )VAR b =SELECTEDVALUE ('VATMonthCommonYear'[Index]) - 4VAR c =SELECTEDVALUE ('VATMonthCommonYear'[Index]) - 2VAR d =CALCULATE (SUM ('VATTotalIncome'[Value]),ALLSELECTED ('VATMonthCommonYear'[MonthYear]),'VATMonthCommonYear'[Index] <= c,'VATMonthCommonYear'[Index] >= b)VAR e =CALCULATE (SUM ('VATAdminCost'[Value]),ALLSELECTED ('VATMonthCommonYear'[MonthYear]),'VATMonthCommonYear'[Index] <= c,'VATMonthCommonYear'[Index] >= b)RETURNIF (SELECTEDVALUE ('VATMonthCommonYear'[Index]) IN a, d * 0.25 + e * 0.25, 0 )This is the logic which works fine. But I wanted this as a calculated column, since i want to use this column in further to do some appendings.If you have an idea on this, could you please help me on this ?
I have two tables first table is original table and second table is having same data but in the original table one value is modified that value is in second table and i dnt know in which coumn and in which table the value is modified please solve my problem
