Forum Discussion
DAX help needed
I woud like to calculate previous month data .
Example: I do have currency exchange rate table which has data until 202008 whenver the current month exchange rates are empty. It has to pick up previous month exchange rates.
sample data.
Anonymous
If the current month Rate is zero or blank then it picks the previous available month's rate: Make sure your Month column is formatted as numbers 202008. Add the following code as a new column to the table.Exchange Rate Complete = VAR SC = [Starting Currency] VAR DC = [Destination Currency] VAR M = [Month] VAR PRMONTH = MAXX(FILTER( Table8, [Starting Currency]=SC && [Destination Currency] = DC && [Month] < M),[Month]) VAR PRRATE = MAXX(FILTER( Table8, [Starting Currency]=SC && [Destination Currency] = DC && [Month] = PRMONTH),[Exchange Rate]) RETURN IF( [Exchange Rate]=0 || ISBLANK([Exchange Rate]), PRRATE,[Exchange Rate])________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
6 Replies
Anonymous
If the current month Rate is zero or blank then it picks the previous available month's rate: Make sure your Month column is formatted as numbers 202008. Add the following code as a new column to the table.Exchange Rate Complete = VAR SC = [Starting Currency] VAR DC = [Destination Currency] VAR M = [Month] VAR PRMONTH = MAXX(FILTER( Table8, [Starting Currency]=SC && [Destination Currency] = DC && [Month] < M),[Month]) VAR PRRATE = MAXX(FILTER( Table8, [Starting Currency]=SC && [Destination Currency] = DC && [Month] = PRMONTH),[Exchange Rate]) RETURN IF( [Exchange Rate]=0 || ISBLANK([Exchange Rate]), PRRATE,[Exchange Rate])________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Hi Anonymous
you need to add a Calendar table in your model in order to use Time intelligence functions.
Do you have it in your model?
Anonymous , you can convert this month to date and use time intelligence
Date =Date(left([Month],4),right([month],2),1)
Or use can use rank for time intelligence, refer my month and week blog for that. Please create a separate month year /date table
like
Month Rank = RANKX(all('Date'),'Date'[Month],,ASC,Dense)
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
Last year Month= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=(max('Date'[Month Rank]) -52)))Time intelligence
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,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)))) previous month value = CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date])) diff = [MTD Sales]-[last MTD Sales] diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales]) last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH))) last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)))) Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month)) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123To 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.Anonymous - You can do it like:
New Exchange Rate Column = IF([Exchange Rate]=0,MAXX(FILTER('Table',[Month] = EARLIER([Month] -1) && [Starting Currency] = EARLIER([Starting Currency]) && [Destination Currency]=EARLIER([Destination Currency])),[Exchange Rate]),[Exchange Rate])
