Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Suhel_Ansari
Helper IV
Helper IV

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

Capture1.PNG

 

1 ACCEPTED SOLUTION

12 REPLIES 12
Greg_Deckler
Super User
Super User

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...


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  ; @amitchandak@Tahreem24 

 

Thanks all

amitchandak
Super User
Super User

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

@amitchandak 
I am checking on that if i can get the date column. Thanks

@Suhel_Ansari 

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/

 

Tahreem24
Super User
Super User

@Suhel_Ansari ,

 

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.

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@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

@Suhel_Ansari ,

 

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.

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 

The format is Whole Number, i can share the pbi file with sample data if you needed. Thanks

@Suhel_Ansari ,

 

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.

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

@Tahreem24 

Sure thing give me somw time 

@Suhel_Ansari ,

 

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.

a.JPG

 

Don't forget to give thumbs up 👍and accept this as a solution if it helped you.

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.