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 da...
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
        )
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.

       

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

        Okay, sounds like it cannot be done with the visual-level filters... which is what I suspected. So we need to determine another way to get the result I need. I do not want to look at how to get a different result.

         

        There are currently no calculations being made. Imagine a table with two columns. Column 1 has a date. Column 2 has a $ amount. If it can't be done, it can't be done. I have already reached this conclusion with several other simple things I am trying to do in Power BI. Things I could do in Excel in minutes. But in Power BI? Impossible. Very disappointing.