Forum Discussion

wnicholl's avatar
wnicholl
Icon for Resolver II rankResolver II
3 years ago
Solved

Set up dates to update without manual intervention

Hi,

I have a waterfall chart (below) that display's 4 separate measures that work and produce the correct totals. Each measure has a different date structure or ask; however, I need to manually update the dates as they are rolling dates.  I’m looking for the dax measures that will eliminate the manual date changes each month.   

  

 

Here are my current measures that support the waterfall chart:

 

2022 Inforce (Note: this is a full prior year only) 

2022 Inforce =

CALCULATE(

    COUNTA('AISCVGP'[ACCUS#]), FILTER('AISCVGP', AISCVGP[ACSTA] = "ACT"), 

    DATESBETWEEN('Calendar'[Date], DATE(2022,1,1), DATE(2022,12,31)

))

 

 

2023 NB (Note: this should be moving forward each month, so next month the dates should be…

DATE(2023,01,01), DATE(2023,05,31) )

2023 NB =

CALCULATE(

    COUNTA('AISCVGP'[ACCUS#]), FILTER('AISCVGP', AISCVGP[ACSTA] = "ACT"), FILTER('AISCVGP', AISCVGP[ACNOR] = "N"),

    DATESBETWEEN('Calendar'[Date], DATE(2023,1,1), DATE(2023,04,30)

))

 

 

2023 Lost / Open Ren (Note: this is a full prior year only) 

2023 Lost/Open Ren =

CALCULATE(

    COUNTA('AISCVGP'[ACCUS#]), FILTER('AISCVGP', AISCVGP[ACSTA] = "NON" || AISCVGP[ACSTA] = "CAN" || AISCVGP[ACSTA] = "LER" || AISCVGP[ACSTA] = "PEN"), ALL('AISCVGP'[ACSTA]), FILTER('AISCVGP', AISCVGP[ACNOR] = "R"), DATESBETWEEN('Calendar'[Date], DATE(2023,1,1), DATE(2023,12,31)

))

 

 

2023 Inforce (Note: this should be rolling prior months, so next month the dates should be… DATE(2022,06,01), DATE(2023,05,31) )

2023 Inforce =

CALCULATE(

    COUNTA('AISCVGP'[ACCUS#]), FILTER('AISCVGP', AISCVGP[ACSTA] = "ACT") ,

    ALL('AISCVGP'[ACSTA]), DATESBETWEEN('Calendar'[Date], DATE(2022,05,01), DATE(2023,04,30)

))

 

Finally, I use this measure to get the 2023 Lost / Open Ren total.

2023 Lost / Open Ren = CALCULATE(AISCVGP[2023 Inforce]-AISCVGP[2022 Inforce]-AISCVGP[2023 NB)

 

Please let me know if you have any questions.

Thank you in advance!  Bill

  • Anonymous's avatar
    Anonymous
    3 years ago

    HI wnicholl,

    I'd like to suggest you add an unconnected date table as source of slicer. Then you can extract the selection values from slicer and modify your formula expressions to use this variable parameterize your conditions to achieve dynamic calculation ranges.

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    HI wnicholl,

    I'd like to suggest you add an unconnected date table as source of slicer. Then you can extract the selection values from slicer and modify your formula expressions to use this variable parameterize your conditions to achieve dynamic calculation ranges.

    Regards,

    Xiaoxin Sheng