Forum Discussion
Previuos Rows Value
Hi DAX Expert,
I am struck in a very interesting issue with my DAX measure issue, I have created measures Count of rows which is Count, M_Reg and M_Ter, Running = M_Reg - M_Ter and Last Year, now i need the another measure Last Year Running which should be previous year Running value as seen in the below image, pLease help me 🙏. Thanks
Regards
Suhel
12 Replies
Do get that first create a year table, as you have take all filter, so that will cause an issue for other filters
Assume year table is
year = dictinct(table[year])//from current table
Last year =CALCULATE(SUM(Sales[Sales Amount]),filter(all(year),year[year]=max(year[year])-1))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedinamitchandak
I am checking on that if i can get the date column. Thanksif you can get date column then create a time table and use formulas.
In can, you only have year you can create date like
date = date(year(table[year],1,1)
These formula's can help. Oce you have date
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))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/
Give a try to below measure:
MEasure = CALCULATE([Running Measure],SAMEPERIODLASTYEAR('Table'[Date]))
Or replace SAMEPERIODLASTYEAR with PREVOIUSYEAR().
Don't forget to give thumbs up 👍and accept this as a solution if it helped you.
Tahreem24
I don't have Date a column in my table, the year is a columns.. so i can't use the Sameperiod DAX Function in this issue. ThanksWhat is the format of your Year column? or share on same value of your Year column.
Don't forget to give thumbs up 👍and accept this as a solution if it helped you.
I like Tahreem24 's suggestion. Tough to say what will work though without sample source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, ee if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008
