Forum Discussion

LP280388's avatar
LP280388
Icon for Resolver II rankResolver 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 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)

 

 

 

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.

4 Replies

  • LP280388's avatar
    LP280388
    Icon for Resolver II rankResolver 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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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