Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
michael_knight
Post Prodigy
Post Prodigy

Specify the last month of data

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

help207.PNG

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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.

View solution in original post

3 REPLIES 3
michael_knight
Post Prodigy
Post Prodigy

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
Helper II
Helper II

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

@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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors