how use TOTALYTD even when not all the data is available at the same period

[Anonymous]

Hello everyone !

I have the following scenario where I need to calculate the YTD value for each channel.

However, for the january, I don't usually have the data for all the channels so when using TOTALYTD, it calculates the YTD of the previous year for some channels year whereas I would like to use the max last avaibale date for all the channels

Instead, I'm using CALCULATE function along with DATESBETWEEN to get the desired output and I was wondering if there is a different way to do it. 

Here is my formula and in comments the section that i don't use anymore 

And here is the result when using TOTALYTD:

As you can see when using the TOTALYTD function, it returns values for all channels, however, it's supposed to only return 11 for "Face to Face" channel since my last avaib date in my table is January and I would like to display the results only for january.

I guess I could change my formula to check if there is no data for the latest available month then return a 0 else use the TOTALYTD formula as showen bellow but I'm still curious if there is another way to use TOTALYTD to "force" a certain context 

Looking forward to your replies,

Cheers,

Mariem

[v-yangliu-msft]

Hi  @Anonymous  ,

I created some data:

1. You can directly specify the date to get YTD

Measure =
CALCULATE(SUM('date'[amount]),FILTER('date','date'[date]>=date(YEAR(TODAY()),1,1)&&'date'[date]<=TODAY()))

 2. Result.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@Anonymous 

Can you share the dummy pbix to resolve it!

[Anonymous]

Hi @VijayP 

I have tried that and it didn't work, here's how i changed my formula:

TOTALYTD (

     [activityMetric],

     FactActivity[MONTHDAY_DT], 

     FactActivity[MONTHDAY_DT] <= monthToUseCY

)

[VijayP]

@Anonymous 

in TOTALYTD try using Year End Date argument , to specify where you Data to end