Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
TomBI
Frequent Visitor

DATESYTD not working on SWITCH with logical test based on time

I am encountering a problem with a DATESYTD on a time based SWITCH measure that I am writing. 

 

Situation:

- I have a measure for my Actuals

- I have a measure for my Forecast

- I have a SWITCH measure that looks at actuals when the week is in the past (based on the WeekIndex_Nr), and at the forecast when the week is in the future

- I want to create a measure that calculates the YTD based on the SWITCH measure

 

Used measures:

 

Actuals = SUM( Fact_Sales[Quantity] )
Forecast = SUM( Fact_Forecast[Quantity] )
Switch ACT/FCST =

SWITCH( TRUE() ,
    SELECTEDVALUE( Dim_Date[WeekIndex_Nr] ) >= 0 , [Forecast] , [Actuals]
)
YTD on Switch ACT/FCST =

CALCULATE(
    [Switch ACT/FCST] ,
    DATESYTD(Dim_Date[Date] )
)

 

Problem: 

I use the DATESYTD measure to calculate the YTD value. Usually this always works, but when I apply it on the SWITCH as described above, the accumulated values are ONLY those of the Forecast, and not of the Actuals. 

 

Result:

 

DATESYTD.png

 

Can anyone explain why this is happening and how to solve it? 

 

Thanks a lot! 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@TomBI , Try like

 

CALCULATE(
Sumx(values( Dim_Date[WeekIndex_Nr] ),[Switch ACT/FCST] ) ,
DATESYTD(Dim_Date[Date] )
)

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@TomBI , Try like

 

CALCULATE(
Sumx(values( Dim_Date[WeekIndex_Nr] ),[Switch ACT/FCST] ) ,
DATESYTD(Dim_Date[Date] )
)

@amitchandak Great! Works perfectly. Can you explain why? 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors