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