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 -
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.
Helper IIChris,
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.