Previuos Rows Value

[Suhel_Ansari]

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

[Greg_Deckler]

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

[Suhel_Ansari · Accepted solution]

@Greg_Deckler  ; @amitchandak ; @Tahreem24 

Thanks all

[amitchandak]

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

[Suhel_Ansari]

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

[amitchandak]

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

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

[Suhel_Ansari]

@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

[Tahreem24]

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

[Suhel_Ansari]

@Tahreem24 

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

[Tahreem24]

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

[Suhel_Ansari]

@Tahreem24 

Sure thing give me somw time 

[Tahreem24]

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

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