Forum Discussion

WishAskedSooner's avatar
WishAskedSooner
Continued Contributor
2 years ago
Solved

YTD Measure not Filtering Properly

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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

5 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    2 years ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      WishAskedSooner
      Continued Contributor
      2 years ago

      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!

    • WishAskedSooner's avatar
      WishAskedSooner
      Continued Contributor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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!