Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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-TIT...
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 Linkedin
if 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. Thanks
What 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.
The format is Whole Number, i can share the pbi file with sample data if you needed. Thanks
Please share your PBI file by masking confidential data if any.
Well, Year should be in Date format not a whole number. This is a catch for incorrect result.
Don't forget to give thumbs up 👍and accept this as a solution if it helped you.
Change your Year column whole number to Date format by Selecting year column --> Go to Modelling Tab --> Under Formatting option --> CHange Data type from Text to Date.
And then use the measure which I have given in previous post.
Don't forget to give thumbs up 👍and accept this as a solution if it helped you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |