cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

Re: Get value from one month ago

Hey, you can use Filter with calculate an Today() function. Try the following expression:

 

LastMonth = CALCULATE([TheMeasure];Table1;month(Table1[Month])=MONTH(TODAY()))

 

Where TheMeasure is the same I posted:

TheMeasure = CALCULATE(SUM(Table1[Actual]); PREVIOUSMONTH(Table1[Month]))

 

Unbenannt.png

Greetings,

Wolf

Highlighted
Frequent Visitor

Re: Get value from one month ago

 Hi Wolf:

Thanks for the suggestion but inexplicably it's a partial solution. I was able to get the correct calculation for this specific example (although I need to change the semicolons in your formula to commas). On the other hand, I don't get the correct values with any other data set even when I used the same Excel table format for the source. That is,  I get unexpected values for  ActualLastMonth and TargetLastMonth, which are inconsistent, even though the values for ActualMeasure and TargetMeasure are correct. Here are 2 screenshots 1) the first dummy data set which worked, and 2) another dummy data set with the wrong values in the cards. I'm including the new measure in both

 

Dataset 1: this workedDataset 1: this workedDataset 2: this didn't workDataset 2: this didn't work

Highlighted
Resolver II
Resolver II

Re: Get value from one month ago

This should do the trick but you could tweak your DAX a bit to make it more concise and readable.

 

IF(

       HASONEVALUE('Table1'[Month]),

       CALCULATE( SUM(Table1[Actual]), PREVIOUSMONTH(Table1[Month]) ),

       CALCULATE( SUM(Table1[Actual]), CALCULATETABLE(LASTDATE(Table1[Month]), NOT(ISBLANK(Table1[Actual])))) 

)

Highlighted
Frequent Visitor

Re: Get value from one month ago

Hi. Thanks for your solution, but it only works for the Actual Last Month measure because that field contains values only through Nov 2017, i.e. the previous month. The Target column shows values to June 2018 so your measure gives a value of 90 (rather than 55 which corresponds to Nov 2017).

 

20171212 Capture.PNG

Highlighted
Frequent Visitor

Re: Get value from one month ago

Hi Phil:

Thanks for your answer, but similar to srivint's measure, it only works for the Actual Last Month measure and not the Target Last Month measure. The Target column contains values to June 2018, and your measure returns a value of 90 rather than 55 

 

20171212b Capture.PNG

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors