Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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).    

 

snapshot data.png

 

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 6
Icey
Community Support
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.

Anonymous
Not applicable

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
Community Support
Community Support

Hi @Anonymous ,

 

If you don't mind, please share me some dummy sample data, not real data, for test.

 

 

Best Regards,

Icey

Greg_Deckler
Super User
Super User

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])


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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])
RETURN
  MAXX(FILTER(Flu_Snapshots,Flu_Snapshots[As Of Date] = __Date),[Actual Value])

 

snapshot data.png

Can you share sample data and sample output.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors