Specify the last month of data

[michael_knight]

Hi,

I've got a set of data that predicts the forecasts. From that data take the last month of the dataset and call it "Problem in the forecasts". I'll post a picture below

As I said above the last month is the "Problem in the forecasts", in this case that month is November 2020. This month will change depending on the data. 

I want to create a measure that counts the values and the number of values in the last month (In this instance it's November 2020). Does anyone know how I can do it? 

I'll add some dummy data below in a PBIX file

https://www.dropbox.com/s/ftn5q3g388azvn5/Completions%20-%20Copy.pbix?dl=0

Cheers,

Mike

[michael_knight]

Cheers @amitchandak  and @danielkrol 

Gave both a go and I think Amit has the best solution for what I'm after. I gave yours a go Daniel but I had problems when using the USERELATIONSHIP function. I do really appreciated both your helps though, thank you very much!

[danielkrol]

I created a measure based on you pbix file to SUM the completions in the last month. Make sure your date table is marked as a date table.
Just change CALCULATE SUM to CALCULATE COUNT for the number of transactions.

value_lastMonth = 
// retrieve last day of last month
VAR _lastMonthE = EOMONTH(LOOKUPVALUE('Date'[Full Date],'Date'[Full Date],MAX(Sheet1[Date])),0)
// retrieve first day of last month
VAR _lastMonthS = DATE(YEAR(_lastMonthE),MONTH(_lastMonthE),1)
// SUM values
VAR _valLastMonth = CALCULATE(SUM(Sheet1[Completions]),FILTER(Sheet1,Sheet1[Date]<=_lastMonthE && Sheet1[Date]>=_lastMonthS))
RETURN _valLastMonth

[amitchandak · Accepted solution]

@michael_knight , I did not check the file yet. But you can get last month like

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

Refer these

https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.