Forum Discussion

AndySmith's avatar
AndySmith
Icon for Helper III rankHelper III
2 years ago
Solved

Month Slicer/Filter not working

Hi

 

I am working wit tabular model and the data comes via our datawarehouse. There are fact tables connected to a date table. Linked by Date Value.

When I bring in Year Month as the rows (for example), all my YTD functions do not work, and they will instead show the value for each month.

When I just use the date field - the YTD amounts calculate correcly. Why would my month slicer be casuing these issues?

 

 

Need Help
  • sergej_og's avatar
    sergej_og
    2 years ago

    Hey AndySmith ,
    I tried to replecate on my model.
    It looks good to me.
    I tried TOTALYTD([Qty], 'Date'[Date], "30/06") -> Comm (1)

    and 

    CALCULATE(
            [Qty],
            DATESYTD('Date'[Date], "30/06")
    ) -> Comm (2)

    Seems to be smtg with your fields in your Date table.
    What is "Date Value" represents?

    Regards

4 Replies

  • sergej_og's avatar
    sergej_og
    Icon for Super User rankSuper User
    2 years ago

     I can see on the left hand side your date is accumulated with a month.
    You can see there is a break between 30/06/2023 and 01/07/2023.
    June is ready accumulated then july starts with accumulation.
    Therefore it seems to give you a correct result on the month-level.

    What is your intension / expectation on the result of your YTD?
    Can you pls provide your YTD formula?

    Regards

  • AndySmith's avatar
    AndySmith
    Icon for Helper III rankHelper III
    2 years ago

    Thanks for the reply. The left hand table - which uses dates as teh rows works fine. Its when I bring in months that the YTD no longer works. I woul dexpect that when Months are applied in the rows - the YTD formual would still calc. - so it would a running total of the month sales. 

    YTD formula is simple : 

    CALCULATE(SUM('Fact Table'[Qty 9LE]),DATESYTD('Date'[Date Value],"30/06"))

     

    • sergej_og's avatar
      sergej_og
      Icon for Super User rankSuper User
      2 years ago

      Hey AndySmith ,
      I tried to replecate on my model.
      It looks good to me.
      I tried TOTALYTD([Qty], 'Date'[Date], "30/06") -> Comm (1)

      and 

      CALCULATE(
              [Qty],
              DATESYTD('Date'[Date], "30/06")
      ) -> Comm (2)

      Seems to be smtg with your fields in your Date table.
      What is "Date Value" represents?

      Regards

      • AndySmith's avatar
        AndySmith
        Icon for Helper III rankHelper III
        2 years ago

        Thanks. There was mismatch of data types for the date field. ALl fixed. Thanks agin