Hello all,
I am having a hard time finding a “Previous Month” totals from a Max Date measure that I already have “Max Date”.
I have a Date table “Date” a “Fact_Dim” table and a “Fact” table in the data model.
Fact_Dim:
Total Exception Amount of Previous Month =
CALCULATE( SUM( Fact[Amount Exception] ),
FILTER( ALLSELECTED( ‘Date’ ),
‘Date’[Date] = [Previous Date]))
Date:
Previous Date =
EOMONTH([Max Date], -1)
Date:
Max Date =
CALCULATE(MAX(Fact[Certification Date]),
FILTER(ALLSELECTED(‘Date’[Date]),
‘Date’[Date] = LASTDATE(Fact[Certification Date] )))
I am trying to get the “Total Exception Amount of Previous Month” to work, but it gives me blanks when putting it in a cross table visual. It works in a card visual as it looks like it’s calculating for the total. Is there a different way to get the previous month total based on a “Max Date” measure?
I use the “Max Date” measure to calculate the last day as I will always have 1 1/2 months lag from current date and I want it to be dynamic.
I have also tried using the below measure "Total Exc Lates Month -1" and incorporate it in my “Total Exception Amount of Previous Month”, but I get a the message “An Argument of Function Date has wrong data type or the result is too small or too large” error, which I am guessing it’s b/c it does not know which date to use.
Fact:
Total Exc Latest Month -1 =
VAR vLastDate = MAX(Fact[Certification Date])
VAR PreMonth = DATE(Year(vLastDate), MONTH(vLastDate) -1, DAY(vLastDate))
RETURN
Thanks,
Saul