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

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

kwpbi -

The sample I provided seems to solve your situation did you try writing:

Daily Sales (Yesterday) = 
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
    )

Placing the [Daily Sales (Yesterday)] measure into the 'Value' area of the Gauge visual to see how it works?

 

If you stay in your Excel mindset you will continue to find difficulties. If you are able to explore the differences, you'll find yourself asking "Why can't Excel do x " more often; in my opinion.

  • kwpbi's avatar
    kwpbi
    Icon for Helper II rankHelper II
    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
        )