Setup:
Two Tables - Data Table and Date Table
Data Table
Date | ValueID | Total |
2021/02/03 | 1 | 20 |
2021/02/02 | 1 | 15 |
2021/01/31 | 1 | 12 |
....... | | |
2021/01/01 | 1 | 8 |
The data is aggrigated at 0100 everyday, the date displays the date when the activity occurred, and the total is an aggrigation preformed on a SQL Table.
More on the date: if the data was aggregated on the 4th, for events on the 3rd – The date will show the 3rd.
Date Table:
The date time is from https://www.sqlbi.com/tools/dax-date-template/
However, just for brevity sake, I am only using 'Date'[Date] and 'Date'[Calendar RelativeMonthPos]
The relationship on the report is ‘date’[Date] 1:* ‘Data’[Date]
What the report does and the problem:
The report displays data from the SQL database to show perfomance on several different items. The report is intended to show month-to-date preformance but because of numerous reasons, this is not data from a production SQL server but rather a SQL server that loads a nightly backup from the production server for reporting purposes. Therefore, the data is always trailing by one day.
Each day, the Total column is summed, averaged, etc, within measures on the report. To show performance of the items from the first of the current month until the current day. Meaning, on Jan 21st 2021, the data displayed is from Jan 1st until Jan 20th.
There is a page filter on 'date'[Date] which is set to "Relative Date" "In This Month" which works fine from the 2nd of a month until the last day of the month.
However, on the 1st of each month, all of the data relates to the previous month. Therefore, the page filter needs to be changed to "Relative Date" "In the Previous 1 Calendar Months" and then returned to "Relative Date" "In This Month" on the second.
Traditionally, I would add a date slicer or a bookmark with a button that would allow the end user to choose which data they would like to see. Unfortunately, the report consumers do not want to change date filters or need to change bookmarks. Therefore, I need to build an “automated” way for the report to show the correct data. This seems like a job for a calculated column to be added to the ‘date’ table. This column would act as the page filter.
The Solution and The Ask:
As stated before, I determined that I needed a calculated column to use as the page filter. I am calling this column “iscurrent” this has two values “Current” or “NotCurrent”
The logic to make the filter is as follows:
IF DAY ( LASTDATE (‘Data’[Date]) = 1 (Example: Only the first then True, Else False)
THEN “IsCurrent” = “Current”
WHERE ‘Date’[Calendar RelativeMonthPos] = -1 (-1 is the previous calendar month
IF DAY ( LASTDATE (‘Data’[Date]) <> 1 (Example: Any day but the first then True Else False
THEN “IsCurrent” = “Current”
WHERE ‘Date’[Calendar RelativeMonthPos] = 0 ( 0 is the current calendar month)
ELSE “NotCurrent”
Here is what I have tried.
First Attempt:
Nested IF Statement with variables:
IsCurrent =
var ltdate = LASTDATE(data[Date])
var MonthDay = DAY(ltdate)
var is1st = IF(MonthDay =1,1,0)
var first = IF('Date'[Calendar RelativeMonthPos] = -1,"Current","Not Current")
var notfirst = IF('Date'[Calendar RelativeMonthPos] = 0,"Current","Not Current")
Return
IF ( is1st = 1, first, IF( is1st = 0, notfirst, “Not Current”)
This sort of worked. However, when the last date on ‘data’[date] is not the first – It shows all of the days from the 2nd to the end of the month as current. When date is the first, this shows all of the same information as if it were the 2nd.
Second attempt:
SWITCH with Variables
IsCurrent =
var ltdate = CALCULATE(
LASTDATE(data[Date]),
ALL(data[Date]))
var MonthDay = DAY(ltdate)
var is1st = IF(MonthDay =1,1,0)
var first = IF('Date'[Calendar RelativeMonthPos]) = -1,"Current","Not Current")
var notfirst = IF('Date'[Calendar RelativeMonthPos]) = 0,"Current","Not Current")
RETURN
SWITCH(
is1st,
1, first,
0, notfirst,
"Not Current"
)
This had the exact same results as the nested if statement. This seems like it would be straight forward, but I am not having success. So, I am putting it to the community to see if anyone has any ideas on how to proceed to make this work.
Thanks in advance for your time!
