Forum Discussion

Darren_Linc's avatar
Darren_Linc
Advocate IV
9 years ago

Tax Year Cumulative

Hi guys,

 

I'm trying to replicate some H&S accident rate data and provide a cumulative accident rate total based on tax year. I've used the following DAX as a column.

 

Cumulative AR =
CALCULATE(
SUM(Accidents_data[Accident Rate1] ),
ALL(Accidents_data),
Accidents_data[Date] <= EARLIER(Accidents_data[Date]))

 

Which provides the cumulative total but doesn't take into account the change and reset behaviour of a new tax year.

 

Rather than create a date dimension table to further complicate something which should be rather simple to do (tax year should be a native setting somewhere) is there any way I can add a filter in the DAX? I have a field which records what tax year which could be used?

 

Any idea's from you wonderful BI folk?

Need Help

2 Replies

  • CahabaData's avatar
    CahabaData
    Memorable Member
    9 years ago

    sounds like you are looking for Year To Date.  here is a generic pattern:

    YTD = TOTALYTD ( SUM ( A ), 'Calendar'[Accounting Date] )

     

    plug in your table/field names as you build this in the formula bar and see if it works for you

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi Darren_Linc,

     

    Not very clear about your requirement, what did you mean "Which provides the cumulative total but doesn't take into account the change and reset behaviour of a new tax year"? How would the tax year be changed? If you want dynamical cumulative accident rate total, perhaps you could try this measure:

    Cumulative AR measure =
CALCULATE (
    SUM ( Accidents_data[Accident Rate1] ),
    FILTER (
        ALLSELECTED ( Accidents_data ),
        Accidents_data[Date] <= MAX ( Accidents_data[Date] )
    )
)

    For further analysis, please provide sample data and screenshot to help us better understand your desired output.

     

    Regards,
    Yuliana Gu