Cant get Previous months Data

[Anonymous]

Hi,

I have a table that has all the month end dates in and products along with there value for that month.

This table is linked to a calendar table (which is a date table). Many to one (both)

In the report I use the month and year from the calendar table to filter the data, this works fine for the month/year (one slicer is year and teh other is month) selected but when I try and show the previous months data alongside I just get blanks.

The data table (GetMRRdata_overall) structure is 

MonthEnd this is linked to date in Calendar

Product

Value

The calc I have tried is 

CALCULATE(SUM(GetMRRdata_overall[MRR]),FILTER(ALL(GetMRRdata_overall),GetMRRdata_overall[MonthEnd] = PREVIOUSMONTH('Calendar'[End Of Month Date]))).

Any help would be much appreciated 

Thank you

[Anonymous]

I Have tried the following but I only get the overall total against all the products not the individual totals :

Measure 3 = VAR SELECTED = MAX('Calendar'[End Of Month Date]) RETURN CALCULATE(SUM(GetMRRdata_overall[MRR]),FILTER(ALL(GetMRRdata_overall),GetMRRdata_overall[MonthEnd] =EOMONTH(SELECTED,-1)))

[Anonymous]

Think I have sorted it (I changed the calendar table name so it didnt confuse with preffered name, this wasnt the answer though)

PreviousMonth = CALCULATE(SUM(GetMRRdata_overall[MRR]),

DATESBETWEEN('t_Calendar'[Date],

FIRSTDATE(DATEADD('t_Calendar'[Date],-1,MONTH)),

IF(MONTH(MIN('t_Calendar'[Date])) = MONTH(SELECTEDVALUE(t_Calendar[End Of Month Date])), EOMONTH(SELECTEDVALUE(t_Calendar[End Of Month Date]),-1) , LASTDATE(DATEADD('t_Calendar'[Date],-1,MONTH)))

)

)

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create a measure like DAX below.

PreviousMonth = CALCULATE(SUM(GetMRRdata_overall[MRR]),DATEADD('t_Calendar'[Date],-1,MONTH))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you Amy, this is much better 🙂