Snapshot data from ERP system aggregating my MTD incorrectly

[Anonymous]

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

[Icey]

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]

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

[Greg_Deckler]

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

[Anonymous]

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

[amitchandak]

Can you share sample data and sample output.