cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BIDrone Regular Visitor
Regular Visitor

Cant get Previous months Data

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xicai Super Contributor
Super Contributor

Re: Cant get Previous months Data

Hi @BIDrone ,

 

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.

4 REPLIES 4
BIDrone Regular Visitor
Regular Visitor

Re: Cant get Previous months Data

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)))
BIDrone Regular Visitor
Regular Visitor

Re: Cant get Previous months Data

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 Super Contributor
Super Contributor

Re: Cant get Previous months Data

Hi @BIDrone ,

 

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.

BIDrone Regular Visitor
Regular Visitor

Re: Cant get Previous months Data

Thank you Amy, this is much better Smiley Happy