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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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



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!:
DAX For Humans

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.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.