YTD is not calculating correct value when there is blank in one of the months

[Ram107568]

I am trying to calculate YTD. Now Cust A and Cust C has orders for both Jan and Feb. Cust B does not have orders for Feb Month. Now if I select Feb month in slicer, Cust B is not showing up. But as cumulative, I need to show 8 for Cust B even if I select Feb in slicer 

Customer	First_day_of_month	No_of_orders	YTD
Cust A	2023/01/01	2	2
Cust A	2023/02/01	3	5
Cust B	2023/01/01	8	8
Cust C	2023/01/01	5	5
Cust C	2023/02/01	2	7

Below is the calculation I use.

YTD1 =

CALCULATE(

    [No_of_orders],

    DATESINPERIOD( Tbl[First_day_of_month] , MAX(Tbl[First_day_of_month]) , -12 ,MONTH)

Appreciate your help on this!

[some_bih · Accepted solution]

Hi @Ram107568 for DATESINPERIOD, you will need Tbl to be your Calendar / Date table. Try to chenge it and see results . (Date / Calendar table must always start on January 1 and end on December 31, including all the days in this range so you should be fine with your issue for blank months).

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

[v-rongtiep-msft · Accepted solution]

Hi @Ram107568 ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a table first.

calendar = CALENDAR(DATE(2023,1,1),DATE(2023,12,31))

 Then create a measure.

Measure =
CALCULATE (
    SUM ( 'Table'[No_of_orders] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[First_day_of_month] <= SELECTEDVALUE ( 'calendar'[Date] )
            && 'Table'[Customer] = SELECTEDVALUE ( 'Table'[Customer] )
    )
)

Finally put the 'calendar'[date] into the slicer.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Ram107568]

Thanks @v-rongtiep-msft for recreating the sample table and helping me with the issue. This solved my problem.

[some_bih · Accepted solution]

Hi @Ram107568 for DATESINPERIOD, you will need Tbl to be your Calendar / Date table. Try to chenge it and see results . (Date / Calendar table must always start on January 1 and end on December 31, including all the days in this range so you should be fine with your issue for blank months).

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

[Ram107568]

Hello @v-rongtiep-msft , what if I had to add date also to the table along with customer and measure. YTD isn't working as expecetd when I add date column to it. The below result is what i get.

For instance, if I select 02/01/2023 in slicer, the result should as follows. Without dates,it's working awesome. If I try to introduce dates, then the trouble starts.

Customer	First_day_of_month	No_of_orders	YTD
Cust A	2023/01/01	2	2
Cust A	2023/02/01	3	5
Cust B	2023/01/01	8	8
Cust C	2023/01/01	5	5
Cust C	2023/02/01	2	7

I had to export this data to excel which acts as source for another snapshot report. So I would be needing date to it

Thanks

[Ram107568]

@some_bih Thanks  for the suggestions. It worked with the calendar table