cancel
Showing results for 
Search instead for 
Did you mean: 
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])


@ 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!:
Mastering Power BI 2nd Edition

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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors