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

[kwpbi]

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!

[ChrisMendoza]

@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]

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]

@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]

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]

@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 · Accepted solution]

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

    )