Forum Discussion

kwpbi's avatar
kwpbi
Icon for Helper II rankHelper II
7 years ago
Solved

Interesting challenge... Conditional relative date filter for gauge-type visual? or other solution?

This is a tricky one for me... I am not a Power BI guru or anything (more like a n00b) so maybe this is super easy for some of you... Hopefully?

 

I am using a gauge-type visual to present sales data. On my gauge for daily sales, it looks at yesterday's data to see if goals were met (it is summing up all invoice entries into a $ total). Simple, right? A relative date filter of past 1 days, without today included, works quite nicely. But here's the issue... for Mondays, I need it to look at the past 3 days, without today included, to capture anything that got invoiced on SAT or SUN (very few occurences of this, so we want to just total it with Friday).

 

I'm kind of stumped on how to approach this one. I don't see a way via the interface to build an 'if' statement into my relative date filtering. If I could, it would say (obviously with correct syntax, etc.): =If(weekday(today())=2,**filter=3 days w/out today**,**filter=1 day w/out today**

 

Thanks in advance for your help!

Need Help
  • kwpbi's avatar
    kwpbi
    7 years ago

    Chris,

     

    Thanks again for getting me going in the right direction. Once I got familiar with the syntax and how to write out the calculate and sum functions, filters, etc. I was able to make this work for me. Here is the final code, very similar to yours but with a couple tweaks. I'm sure there are other ways to do this, but this is working well now. Thanks.

     

    Daily Sales (Yesterday) =
    VAR _mondayCalc =
        IF (
            WEEKDAY ( TODAY (), 3 ) = 0,
            (CALCULATE ( SUM ( YTD_Invoices[INVOICEAMOUNT] ), YTD_Invoices[INVOICEDATE] = TODAY () - 3 ))+(CALCULATE ( SUM ( YTD_Invoices[INVOICEAMOUNT] ), YTD_Invoices[INVOICEDATE] = TODAY () - 2 ))+(CALCULATE           ( SUM ( YTD_Invoices[INVOICEAMOUNT] ), YTD_Invoices[INVOICEDATE] = TODAY () - 1 ))
        )
    RETURN
        IF (
            WEEKDAY ( TODAY (), 3 ) > 0,
            CALCULATE ( SUM ( YTD_Invoices[INVOICEAMOUNT] ), YTD_Invoices[INVOICEDATE] = TODAY () - 1 ),
            _mondayCalc
        )

6 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    kwpbi -

    Does this work for your needs?

     

    Measure = 
VAR _mondayCalc =
    IF (
        WEEKDAY ( TODAY (), 3 ) = 0,
        CALCULATE ( SUM ( TableName[Sales] ), WEEKDAY ( TableName[Date], 3 ) >= 4 )
    )
RETURN
    IF (
        WEEKDAY ( TODAY (), 3 ) > 0,
        // Used for Testing Previous Day Calculation
        //WEEKDAY ( TODAY (), 2 ) > 0,
        CALCULATE ( SUM ( TableName[Sales] ), TODAY () - TableName[Date] = 1 ),
        _mondayCalc
    )

     

    • kwpbi's avatar
      kwpbi
      Icon for Helper II rankHelper II
      7 years ago

      I am not trying to add a measure to my visual, I am trying to apply a different "visual level filter" on mondays only (in other words, if TODAY() is monday, report a 3-day total. For all other days, report a 1-day total.

       

      Attached is what the table should look like when correct (this is with a relative date filter of 3 days, not including today... because today is Monday).

      I also attached what it looks like when the filter is set to only 1 day, which is what I need for all other days.

      This is what I need for Mondays (3 days back)This is 1 day back, which doesn't work for Mondays

       

      • ChrisMendoza's avatar
        ChrisMendoza
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        kwpbi -

        How are you calculating Daily Sales ( Yesterday ) ? While it is convienent to use the built-in tool of 'Filters on this visual', as far as I know, it is not possible to add that logic there. This is why I attempted to write a measure to make the correct calculation. You should include sample data or your file so the community can assist you further.