Get data for the last month from a table and measures

[Wisselaar]

Hi,

I'm trying to get some KPI's for a dimension (DQ Dimension). I have the data available in a table 'ErrorsTotalsMonth' and various measures (Error %, Error % Prev mont, MOM, Avg. Target, Treshold). The measures all work in report, but I'd like to create a dashboard with the last month of available data in an overview. Now, if I try the DAX below, that doesn't work, right. The previous month is gone. Any ideas how this could work? (Filter in the report with the last month or something is not the solution I'm looking for here, since I need Join the summarized table.

SUMMARIZE(
FILTER(
ErrorTotalsMonth;
ErrorTotalsMonth[Date End Of Month].[Date] = LASTDATE(ALL(_CalenderTable[EOM]))
);
DQ_Rule[Data Quality Dimension];
"Avg Error % EOM"; DIVIDE(ErrorTotalsMonth[Error % EOM If Error]; 100);
--"Avg Error % Prev Month"; ErrorTotalsMonth[Error % Prev Month];
"Avg Threshold"; ErrorTotalsMonth[Avg Threshold];
"Avg Target"; ErrorTotalsMonth[Avg Target];
--"MOM"; ErrorTotalsMonth[Error % MOM %];
--"UpDown"; ErrorTotalsMonth[KPI Error % Month (Indicator)]
)

[Wisselaar · Accepted solution]

In the end I solved it, by creating a column in a globals-table with the last month. From there I could get all the measures to work:

Error % Prev Month Of Last Month =

CALCULATE(ErrorTotalsMonth[Error % EOM If Error]; DATEADD(Globals[Last Month of Data Date]; -1; MONTH))

[amitchandak]

@Wisselaar , with help from date table and time intelligence?

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Wisselaar]

Hi,

As mentioned I have all these measures, but need them in a table. How would you create a table with these measures and only the last month of data.