Running total for last year

[LP280388]

Hi Team,

I have a table with the data as below. I have calculated the current year count and running total for current year. however, finding it difficult to calculate the same for last year.   I cant use "sameperiodlastyear" or any other date functions because we dont have date column. all we have is Type column which has data as below .  So I'm calculating the lastyear/period as below 

var lastyear = REPLACE( SELECTEDVALUE('Summary'[Type]),14,1, MID(SELECTEDVALUE('Summary'[Type]),14,1)-1)

and trying to send this to the filter. however, I'm successful in getting the last year values. However, not able to find a way to calculate the running total for the same.  

Kindly help me what im missing here.  

Type: 

XYZ - Q1-2021

XYZ - Q1-2022

XYZ - Q1-2023

XYZ - Q2-2021

XYZ - Q2-2022

XYZ - Q2-2023

XYZ - Q3-2021

XYZ - Q3-2022

XYZ - Q3-2023

Lastyear1 =

var lastyear = REPLACE( SELECTEDVALUE('Summary'[Type]),14,1, MID(SELECTEDVALUE('Summary'[Type]),14,1)-1)

var maxdate = CALCULATE(MAX('Summary'[END_DATE]),'Summary'[Type]=lastyear)

return CALCULATE(DISTINCTCOUNT('Summary'[ID]),'Summary'[END_DATE]<= maxdate,'Summary'[Type]=lastyear)

[LP280388 · Accepted solution]

Hi Team,

Thanks for your responses. I agree and love the date table all the time.  As I said I couldnt add the DateTable due to different dates for different Types.  So I finally was able to do it by creating a supporting column as below. 

I found that we have start date and end date and based on these columns I created a column

"DayCount = EndDate - Startdate "

 which gave the output as 1,2,3 and so on.... then I based the maxdate variable based on this and 

So my final query now looks like which worked in my situation:

Lastyear1 = 

var lastyear = REPLACE( SELECTEDVALUE('Summary'[Type]),14,1, MID(SELECTEDVALUE('Summary'[Type]),14,1)-1)

var maxdate = CALCULATE(MAX('Summary'[DayCount]),'Summary'[Type]=lastyear)

return CALCULATE(DISTINCTCOUNT('Summary'[ID]),'Summary'[DayCount]<= maxdate,'Summary'[Type]=lastyear)

thanks everyone for helping me.

[v-shex-msft]

HI @LP280388,

I'm glad to hear you find the solution and share the formula here, they should help others who faced the similar scenario.

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @LP280388,

Time intelligence function requirement a calendar table with general date values.

According to your description, it seems like you are used the text date values which not supported in time intelligence functions.

For this scenario, I'd like to suggestion add a calculated column to convert the value to date values, then you can use DATE function to extract this value parts as condition to filter date ranges in your expressions.

Regards,

Xiaoxin Sheng

[lbendlin]

That's not sustainable. Add a calendar table to your data model, and add a date column to your fact table.