Forum Discussion

LP280388's avatar
LP280388
Resolver II
2 years ago
Solved

Running total for last year

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...
Need Help
  • LP280388's avatar
    LP280388
    2 years ago

    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.
LP280388's avatar
LP280388
Resolver II
2 years ago

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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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