YTD Value Formula adjusted by date slider

[Dannismen]

Hi all, 

Can someone plz revise the below formula so it will be adjusted by [Fiscal_BOW] date slider and always start with 0. 

Current Year =

VAR __to = MAX(Qry_6999_UNION_ALL[Fiscal_BOW] )

VAR __from =

MINX(

CALCULATETABLE(

Qry_6999_UNION_ALL,

Qry_6999_UNION_ALL[Fiscal_BOW] ,

Qry_6999_UNION_ALL[Fiscal_BOW] <= __to

),

Qry_6999_UNION_ALL[Fiscal_BOW]

)

RETURN

CALCULATE(

SUM( Qry_6999_UNION_ALL[Metric_Value] ),

DATESBETWEEN( Qry_6999_UNION_ALL[Fiscal_BOW], __from, __to )

)

[Dannismen]

The current visual shows the cumulative starts the first day of 2021 but I want it to show the one that starts with the date slider. 

[v-xulin-mstf]

Hi @Dannismen

You need to create the date dimension for the date slicer, associate the measure to that date dimension and put it in the x-axis of visua.

For more information about creatint dimension table, you can refer:

One Dimension Filters Another Dimension in Power BI - RADACAD

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[Dannismen]

Hi, 

Thanks for your reponse but it still showed the same thing when I did what you suggested. I think it's the matter of the formula. Would you mind take a look? Thanks

[v-xulin-mstf · Accepted solution]

Hi @Dannismen

You can try measure as:

Current Year = 
VAR __to = CALCULATE(ALLSELECTED(Date_slicer[Date]),Date_slicer[Date])
VAR __from =CALCULATE(MINX(ALLSELECTED(Date_slicer[Date]),Date_slicer[Date]))

RETURN
CALCULATE(
SUM( Qry_6999_UNION_ALL[Metric_Value] ),
FILTER(
    Date_slicer,
    DATESBETWEEN( Qry_6999_UNION_ALL[Fiscal_BOW], __from, __to ) && Date_slicer[Date]=MAX(Qry_6999_UNION_ALL[Fiscal_BOW])
)
)

If you expect the date slice range to be larger than the date column in the table, you need to create the date dimension for the date slicer, and put it in the x-axis of visual.

Here is the output:

The pbix is attached.

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!