Show data from current month, or if today is the 1st then show me last month

[TheStu82]

Hello

I have a query and associated report which details all month to date sales figures and how these figures are trading against that months target. Users have to input the new sales figures each morning for the previous day into a sharepoint list, the dataset then refreshes at 8.30am and and an email is triggered to everybody who is subscribed to the report.

The report is built to show all data for the current month using the following DAX statement.

IsCurrentMonth =
IF (
YEAR ( DateKey[Date] ) = YEAR ( TODAY () )
&& MONTH ( DateKey[Date] ) = MONTH ( TODAY () ),
"True",
"False"
)

This works perfectly most days, however I am anticipating an issue on the first day of a month. For example, on the 1st June, users will be inputting fugures for the previous day - 31st may. When the report refreshes and sends the subscriber email though, it will do so using the IsCurrentMonth criteria which will be June. Therefore the report will be blank.

This of course will correct itself on the 2nd June, but it will mean that there is never a full, end of monthreport sent - unless i do it manually.

Is there an ajustment which i can make to the statement to say show me current month, unless today's date is the 1st, in which case show me last month?

Any help would be greatly appreciated

cheers

[lalthan · Accepted solution]

Try this. Works for me as we also have a 1 day delay. You may also replace "Other Months" with the actual Month Yr value from your Period table

"Current Month",switch(true(),month(Period[Date])=month(today()-1) && YEAR(Period[Date]) = YEAR(TODAY()-1),"Current Month","Other Months")

[TheStu82]

Thanks for getting back to me so quickly, this worked a treat.

It has however thrown up another related error which hopefully you may be able to help me out with. 

Within the query, i have a column called Forcast sales which uses the sales so far in the month along with the current date and predicts how many sales will be made by the end of that month.

Total Sales + ((Total Sales/day of month) * days remaining)

Because the report is a day behind, I use yesterday's day (Todays Day - 1) as the basis of the calculations, however, when on the 1st of a month, it is prodcuing a figure of 0 so is producing an error.

Is there an easier way to show yesterday's day number - and one that will work on the first day of the month, so show the last day of the previous month?

Thanks again for your help, any further assistance woudl be appreciated

[lalthan]

Ideally, you should implement your logic in your Date calendar table. I am assuming that your "Is Current Month" is a calculated column in your DateKey table.

Create two columns which will get processed on a daily basis

1. Total Working days i.e. 1 for each working date and 0 for non working days

2. Days Completed i.e. For each value of 1 in your total working days as per point 1, update as 1 if your transactions are already available otherwise 0.

Your forecast sale measure should be:  Total Sales/sum([Days Completed] * sum([Total Working Days]

Once this is done, your filter on "Is Current Month" will take care of the rest 🙂

[TheStu82]

Hi, thanks again for your response, however I'm not really following what you are meaning.

I do have a DateKey table and yes, the IsCurrentMonth column is within this table.
I sort of understand the idea behind the formula, but the working days bit has me baffled, sorry.

Your help is greatly appreciated