DAX help needed

[Anonymous]

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.

[Fowmy · Accepted solution]

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

YouTube  LinkedIn

[Anonymous]

@Fowmy This working fine. Let me do further testing. Thanks a lot.

[Anonymous]

@Fowmy Thanks a lot . Its working fine.

[Greg_Deckler]

@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])

[amitchandak]

@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/1051123

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[aj1973]

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?