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