Forum Discussion
Snapshot data from ERP system aggregating my MTD incorrectly
Hello Community - So, we have an ERP system that spits out "snapshot data". I am not very familiar with working with snapshot data, but I have what I think is a fairly simple problem. I have a date table that is related to the "as of date" in the Flu_Snapshots table. My simple issue is that, because this is snapshot data of every single day, the MTD is capturing every single snapshot value for the month...which aggregates to a ridiculous amount. I just need the value of the last-most date of the period (in this case, month).
Below you will see the current DAX formula, and the results it gives in two different visuals. The problem is, because the "actual value" and "as of date" are snapshot data, the MTD formula is summing every snapshot period.....when all I need is the LAST date of each period (in this case month). You can see in the table below the value for Feb 29 and for March 5.
What I need is the value for the ending period of Jan Feb and the current MTD for this month (March).
As Of MTD = CALCULATE(SUM(Flu_Snapshots[Actual Value]),DATESMTD(Flu_Snapshots[As Of Date]),
FILTER
( Flu_Snapshots, ( Flu_Snapshots[Description])
IN { "FLU All Revenue Accounts YTD - 40000", "FLU All Revenue Accounts YTD - 40005", "FLU All Revenue Accounts YTD - 40010", "FLU All Revenue Accounts YTD - 40075", "FLU All Revenue Accounts YTD - 40300", "FLU All Revenue Accounts YTD - 40400", "FLU All Revenue Accounts YTd" } ))*-1
6 Replies
- Greg_Deckler
Community Champion
So generally you would use MAX or MAXX to find the last date in your desired period. Then you would use that in LOOKUPVALUE or in FILTER to zero in on just the value that you wanted.
So, something along the lines of:
Measure =
VAR __Date = MAXX('Table',[Date])
RETURN
MAXX(FILTER('Table',[Date] = __Date),[Value])
Greg_Deckler Thanks Greg - When I apply this formula, my data looks like this. I tried adding in the information from my "IN" statement in my original formula, but I kept getting an error message (something about multiple columns can't work with scalar values).
So your formula definitely got me closer, just need to figure out why it's not giving me the correct values per month. (I do have the date table connected to the "as of date" in my Flu_Snapshots table)
Measure =VAR __Date = MAXX(Flu_Snapshots,Flu_Snapshots[As Of Date])RETURNMAXX(FILTER(Flu_Snapshots,Flu_Snapshots[As Of Date] = __Date),[Actual Value])- amitchandak
Super User
Can you share sample data and sample output.
- Icey
Community Support
Hi Anonymous ,
Try this:
Measure = VAR __Date = MAXX ( ALLEXCEPT ( Flu_Snapshots, Flu_Snapshots[MonthName] ), Flu_Snapshots[As Of Date] ) RETURN MAXX ( FILTER ( ALLEXCEPT ( Flu_Snapshots, Flu_Snapshots[MonthName] ), Flu_Snapshots[As Of Date] = __Date ), [Actual Value] )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Icey,
Your formula is not working because I think it is totalling ALL of the actual value amount. I need the values in this "IN" statement to only be included. I tried adding it into your formula, but could not get it to work.
If you could add this into your formula, maybe it would work?
IN { "FLU All Revenue Accounts YTD - 40000", "FLU All Revenue Accounts YTD - 40005", "FLU All Revenue Accounts YTD - 40010", "FLU All Revenue Accounts YTD - 40075", "FLU All Revenue Accounts YTD - 40300", "FLU All Revenue Accounts YTD - 40400", "FLU All Revenue Accounts YTd" } ))*-1- Icey
Hi Anonymous ,
If you don't mind, please share me some dummy sample data, not real data, for test.
Best Regards,
Icey
