YTD Measure not Filtering Properly

[WishAskedSooner]

Hi Power BI Experts!

I have a 'Fact' table with columns for [CustomerID], [OrderDate], and [Amount], and two dimension tables, 'Customer' and 'Date', with one-to-many relationships to the 'Fact' table.

I want to plot the Current YTD against the entire prior YTD, so I also have an 'UnconnectedDate' table with a CurrYear calculated column that allows me to pull in the Months from that table into the X-axis with a filter on the CurrYear.

Everything is working fine with one exception. When I add a slicer to the page for CustomerID, it doesn't filter the YTD properly. Customer A's Orders for June have been loaded into the data model, but I am still waiting on Customer B's Orders for June and only have Orders through May. However, when I filter on Customer B, the YTD measure displays a value for June anyway which screws things up because the value is flat vs May since the June data is not yet available. It is critical that I fix this otherwise my YTD reports are simply garbage.

Here is my measure:

YTD_Orders =

    VAR YTD_Months = DATESYTD(TREATAS(VALUES('UnconnectedDates'[Date]), 'ConnectedDates'[Date]))

    VAR Res = CALCULATE(SUM('Fact'[Amount]), YTD_Months)

RETURN

    Res

I have tried a bunch of different ideas with no success. I even made a date table that included a column for CustomerID, but that didn't work either.

I simply cannot figure out how to get my measure to return values through just the months for which data exists when a filter context is applied.

Thanks in advance for your help!

[Anonymous]

Hi @WishAskedSooner ,

Thank you for your reply ,@Samarth_18 . Based on the description of the problem, I tried some solutions. Please try the following DAX code:

YTD_Orders =
    VAR YTD_Months = DATESYTD(TREATAS(VALUES('UnconnectedDates'[Date]), 'ConnectedDates'[Date]))
    VAR ExistingMonths =
        CALCULATETABLE(
            VALUES('ConnectedDates'[Date]),
            'Fact'
        )
    VAR FilteredYTD_Months =
        FILTER(
            YTD_Months,
            'ConnectedDates'[Date] IN ExistingMonths
        )
    VAR Res = CALCULATE(SUM('Fact'[Amount]), FilteredYTD_Months)
RETURN
    Res

Give this a try and let me know if it resolves your issue! If you have any questions or need additional adjustments, please provide us with the sample pbix after removing sensitive data so that we can better help you solve your problem.

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[WishAskedSooner]

Unfortunately, I am not able to post a sample PBIX due to security restrictions. However, it is pretty easy to re-create this problem. Below is a sample Fact table I quickly made in Excel and loaded into PBI:

And created the following data model:

I then created the following measures modeled on what has already been posted: OrigYTD, Samarth_YTD, Zhu_YTD. And this is what I am getting:

So, none of the methods are working properly. I hope that this helps diagnosing my problem and generating a solution.

Thank you!

[Anonymous · Accepted solution]

Hi @WishAskedSooner ,

I tried to create a sample data myself based on yours’ description and implemented the result. Please check if there is anything that can be improved. Here is my solution:

1.Create simple data:

The relationship between them is:

2.Create measures:

PYTD1 = CALCULATE(
     SUM('Fact'[Amount]),
     SAMEPERIODLASTYEAR(TREATAS(VALUES(UnDimDate[Date]),'DimDate'[Date]))
)


PriorYTD=CALCULATE([PYTD1],FILTER(ALL('UnDimDate'),'UnDimDate'[Date<=SELECTEDVALUE(UnDimDate[Date])))

With the following measure, you can achieve a cumulative effect:

YTD_orders = 
VAR YTD_Orders1 = 
    VAR YTD_Months = DATESYTD(TREATAS(VALUES('UnDimDate'[Date]), 'DimDate'[Date]))
    VAR ExistingMonths =
        CALCULATETABLE(
            VALUES('DimDate'[Date]),
            'Fact'
        )
    VAR FilteredYTD_Months =
        FILTER(
            YTD_Months,
            'DimDate'[Date] IN ExistingMonths
        )
    VAR Res = CALCULATE(SUM('Fact'[Amount]), FilteredYTD_Months)
RETURN
    Res
VAR _day  =SELECTEDVALUE(UnDimDate[Date])
RETURN IF(CALCULATE(SUM('Fact'[Amount]),'Fact'[OrderDate]=_day)<>BLANK(),YTD_Orders1)

3.The final result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[WishAskedSooner]

First, thank you so much for both of your replies. I am always appreciative for any and all ideas. And in that spirit, I tried implementing both solutions.

Sadly, Samart_18's did not work as hoped, but I am not too surprised because the filter on the page should propagate through the measure not requiring a explicit addition of that filtered value. But, what do I know? Thanks, nevertheless.

Zhu's solution gets me closer. The YTD value now does not extend beyond the existing months for Customer B. Success! I am very happy about that. However, instead of calculating the running cumulative sum like before, it now just returns the monthly sum. How can we get back to the running cumulative sum?

Below is a screenshot of where I am at.

Thanks!

[Samarth_18]

Hi @WishAskedSooner ,

You could try below code:-

YTD_Orders = 
VAR SelectedCustomer = SELECTEDVALUE('Customer'[CustomerID])

VAR YTD_Months = 
    DATESYTD(TREATAS(VALUES('UnconnectedDates'[Date]), 'ConnectedDates'[Date]))

VAR YTD_Orders_CustomerFiltered = 
    CALCULATE(
        SUM('Fact'[Amount]),
        'Customer'[CustomerID] = SelectedCustomer,
        YTD_Months
    )

RETURN
    YTD_Orders_CustomerFiltered