Forum Discussion

Belle2015's avatar
Belle2015
Icon for Helper II rankHelper II
1 year ago
Solved

Help with error when creating a measure for a date filter

Hi,  I have had some help in creating a measure to create a filter to show the previous day's data or the weekend's data on a Monday but I am getting the below error 'The expression refers to multip...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Belle2015 

    Based on your information, I create you table. At the same time, I added a piece of data from January 6th to test it.

    Then create a new measure:

     

    PreviousDayOrWeekendData = 
VAR TodayIsMonday = WEEKDAY(MAX('Table'[Count Date]), 2) = 1
VAR _PreviousDay = CALCULATE(SUM('Table'[TotalStockPlusDiff]), 'Table'[Count Date] = MAX('Table'[Count Date]) - 1)
VAR WeekendData = CALCULATE(SUM('Table'[TotalStockPlusDiff]), WEEKDAY('Table'[Count Date], 2) IN {6, 7} && 'Table'[Count Date] <= MAX('Table'[Count Date]))
RETURN
IF(TodayIsMonday, WeekendData, _PreviousDay)

     

     

    Put this measure in table visual and create a slicer. Here is my preview:

     

    When you select a slicer, the metrics also change. At the same time, it calculates the sum of the weekends up to the date selected by the current slicer.

     

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Icon for Super User rankSuper User
1 year ago

Hi Belle2015 ,

I just restructured your DAX to return the the desired scalar value, please try the bellow code and let me know if its all ok:

FilteredByDate = 
VAR TodayDate = TODAY()
VAR DayBefore = TodayDate - 1
VAR IsMonday = WEEKDAY(TodayDate, 2) = 1
RETURN
    CALCULATE(
        SUM('Table 2'[Difference Recorded]),
        FILTER(
            'Table 2',
            IF(
                IsMonday,
                'Table 2'[Count Date] = TodayDate - 3 || 
                'Table 2'[Count Date] = TodayDate - 2 || 
                'Table 2'[Count Date] = TodayDate - 1,
                'Table 2'[Count Date] = DayBefore
            )
        )
    )
  • Belle2015's avatar
    Belle2015
    Icon for Helper II rankHelper II
    1 year ago

    Thanks so much for your help @Bibiano_Geraldo

    Maybe I am not using this correctly but I though that I would just apply this measure to the filters to work but it is not filtering the table! Sorry I am very basic with this and trying to learn!

     

     

    Preview
     
     
     
    • Bibiano_Geraldo's avatar
      Bibiano_Geraldo
      Icon for Super User rankSuper User
      1 year ago

      Hi whats is your expected resultt based on data you provided? i'll make some logic here to filter your data, just give me more context.

       

      Thank you

      • Belle2015's avatar
        Belle2015
        Icon for Helper II rankHelper II
        1 year ago

        Thanks for helping 🙂

        I am trying to look at the counts done the previous day so as we can see what % of stock was used or added in, on a Monday I want to be able to see what was counted for on the Friday, Sat and Sunday. 

        The table may have multiple counts done on the same product and it can be counted more than once on the same day. 

        Hope that explains it ok.