Can't get measures to step back one day (we report as of yesterday). TODAY()-1 is inconsistent

[Anonymous]

This is regarding automated reporting for sales tec. using RELATIVE filter. No Month filter

We have a calendar that has future dates. I've never had to work with that. At my last job Microsoft consultants made us a calendar that stopped the prior days date.  In fact, they had two calendars and one did have future dates.

We report effective YESTERDAY. That becomes more of an issue the first day of the month.

So I subtracted 1 from TODAY(). 

This DAX works:

Sales_USD_MTDCurrent_Net = CALCULATE( FACT_Sales[Sales_USD_Net_Dis],

FILTER( BI_Calendar , BI_Calendar[Month] = MONTH( TODAY() -1 ) ) )

This and none of the calculations in the Calendar table does this work. It still is returning the new current month number. (1 example):

IsWorkingDayCompleted = Calculate( SUM(BI_Calendar[IsWorkingDay_NUM] ),

FILTER( BI_Calendar ,

AND( BI_Calendar[Month] = MONTH(TODAY() -1 ),

BI_Calendar[Date_Sales] = TRUE() ) ) )

Then I try this and it won't work:

Error message is A function 'CALCULATE' has been used in a TRUE/FALSE expression that is used as a filter expression. This is not allowed.  

IsWorkingDayCompleted = // calculates the  Sum of working days completed in the month

VAR latestDate = MAX(FACT_Sales[CalendarKey])

VAR prevCalDay = CALCULATE(MAX(FACT_Sales[CalendarKey]),FACT_Sales[CalendarKey]= latestDate)

RETURN

CALCULATE(SUM(BI_Calendar[IsWorkingDay_NUM] ) ,

FACT_Sales[Sales_USD_Net_Dis],

FACT_Sales[CalendarKey]=prevCalDay )

I assume it is the [BI_Calendar[IsWorkingDay_NUM] and that returns a number (1 for each day).

Calculated as:  IsWorkingDay_NUM = IF( BI_Calendar[IsWorkingDay] = TRUE() , 1,0 )

PLEASE HELP and thank you. This is very frustrating.  I give KUDOS and mark as Solved!

[AlB]

Hi @Anonymous 

The error message you get is easy to fix but I do not quite get what the overall problem is and waht you intend to do. Could you plase explain it a bit more, ideally basing the explanation on sample data?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

The issue is: The Calendar has future dates but we report as of yesterday. The big problem is when it is the first day of the month, say October 1. We are reporting September and the calendar tells the report it is October. 

I can't figure out how to tell measures to look back 1 day. 

I wonder if a simple solution is to make a column in Calendar that is Accounting_Date and have that one day behind. I don't know how to do that. I know how to make calendar columns but not one that is one day behind. 

[V-pazhen-msft]

@Anonymous 

I am not sure with the following filter expression you provided:

FILTER( BI_Calendar ,

AND( BI_Calendar[Month] = MONTH(TODAY() -1 ),

BI_Calendar[Date_Sales] = TRUE() ) ) )

MONTH(TODAY() -1 ) only gives you the month of the yesterday date. So if today is 5th Oct, MONTH(TODAY() -1 ) returns you the month of yesterday, which is still October. 

If you want to filter to 1 day before, you should just filter with [date] column instead of [month]:

BI_Calendar[Date] = TODAY() -1
If you want to filter to 1 month before, the filter expression should be: 

BI_Calendar[Month] = MONTH(TODAY())-1

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@V-pazhen-msft 

On the first day of the month I want to report last month. The issue is this formula I expected look to September on Oct 1 and it did not ( [IsWorkingDay_NUM] = 1 for a working day):

This calculates the number of working days worked in the reported month

IsWorkingDayCompleted = Calculate( SUM(BI_Calendar[IsWorkingDay_NUM] ),

FILTER( BI_Calendar ,

AND( BI_Calendar[Month] = MONTH(TODAY() -1 ),

BI_Calendar[Date_Sales] = TRUE() ) ) )

[watkinnc]

Instead of = Calculate( SUM(BI_Calendar[IsWorkingDay_NUM] ),

FILTER( BI_Calendar ,

AND( BI_Calendar[Month] = MONTH(TODAY() -1 ),

BI_Calendar[Date_Sales] = TRUE() ) ) 

try

= Calculate( SUM(BI_Calendar[IsWorkingDay_NUM] ),

FILTER( BI_Calendar ,

BI_Calendar[Month] = MONTH(TODAY() -1 )&& BI_Calendar[Date_Sales] = "TRUE" )