Forum Discussion
Calculating Current Period
Hi
I would like to calculate current period for e.g even though if I update any data in this month August(any day) it should still display the data till last month July(end).
The reason I'm doing this so that I can create a custom column with current period 1(which would be my 'actual') and other as 0 which would be my 'forecast' and I can use this column for condition formating to different colours.
Please advise.
4 Replies
chiru5262 , You do with time intelligence
If want only last month
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))this month MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
If you need ytd
YTD QTY forced=
var _max =eomonth( today(),-1)
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))All sales
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=eomonth(maxx(date,date[date]),-1)))
Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090eTo 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.chiru5262
There a few dax expressions can accomplish that, for example:Measure = Calculate(sum([data],Filter(All(Table),Month([Date])<=Month(Calculate(MAX([Date]),All(Table)))-1))If you post some sample data, we should able to provide a more definite dax solution.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Guys,
Appreciate your reply, I'm gonna try but here is the sample screenshot what I'm trying to acheive.The line value is what I would like to change the colours where the current period (July) is actual represent one colour and the rest from Aug to Jun as forcast represent another colour
Line Value is it I would like to change for current period (July) as one colour and the rest as Forecast(one colour)
chiru5262
Have you reached a solution with previous dax formulas? I noticed if you have two or more years data, you also need to add year in the filter expression:Measure = Calculate(sum([data],Filter(All(Table),Month([Date])<=Month(Calculate(MAX([Date]),All(Table)))-1), Year[Date]<=Year(MAX[Date]))
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
