Forum Discussion
Helper IIInteresting challenge... Conditional relative date filter for gauge-type visual? or other 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 )))RETURNIF (WEEKDAY ( TODAY (), 3 ) > 0,CALCULATE ( SUM ( YTD_Invoices[INVOICEAMOUNT] ), YTD_Invoices[INVOICEDATE] = TODAY () - 1 ),_mondayCalc)
Resident Rockstarkwpbi -
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
)
Helper III 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.