Power BI Slicers - Default Year / Month / Day – First Day of the Month Issue

[danvrabie]

Hello,

I have created a default slicer selection for Power BI, by creating 3 columns in a Calendar Table, as follows:

CurrentYear =

VAR __dif_year = DATEDIFF('Calendar'[Date],UTCNOW()-(3/24),YEAR)

return

IF(__dif_year = 0, "This Year", IF(__dif_year >0, FORMAT('Calendar'[Date],"YYYY")))

CurrentMonth =

VAR __dif_month = DATEDIFF('Calendar'[Date],UTCNOW()-(3/24),MONTH)

return

IF(__dif_month = 0, "This Month", IF(__dif_month >0, FORMAT('Calendar'[Date],"MMMM")))

CurrentDate =

VAR __dif_day = DATEDIFF('Calendar'[Date],UTCNOW()-(3/24),DAY)

return

IF(__dif_day = 1, "Yesterday", IF(__dif_day >0, FORMAT('Calendar'[Date],"YYYY-MM-DD")))

Each dashboard page has 3 slicers: CurrentYear: “This Year”, CurrentMonth: “This Month”, CurrentDate: “Yesterday”, which filters the dashboard values as of yesterday.

The complication I’m running into is the first of each month, when Current Month = “This month” returns the current month, while CurrentDate = “Yesterday” returns the date from the previous month. For example, on April 1st, the Month slicer should return April values, while the Date slicer should return March 31st values. Given the conflict between the Month Slicer and the Date Slicer, no values are returned.

Is there a way I can define the CurrentMonth calculation / slicer so that:

• If the current date is the first of the month, the previous month is labelled “This Month”
• If the current date is not the first of the month, the current month is labelled “This Month”?

Link to mockup file here: SlicerMockup.pbix.

The “Slicer – Existing” tab shows the current issue, while the “Slicer – Expectation” shows the expected result, assuming a “This Month” + “Yesterday” combination.  

Thank you for your suggestions.

[v-achippa · Accepted solution]

Hi @danvrabie,

Thank you for reaching out to Microsoft Fabric Community.

The problem with the initial CurrentMonth Measure was that it directly compared the difference between each date in the Calendar table and the current date using DATEDIFF. When today is the 1st of the month, this comparison would return 0, indicating that it's still within the same month. But for your business logic, you actually wanted the previous month (March) to be labeled as "This Month" when it's the 1st of April, rather than showing April itself.
To address this, we revised the DAX by adding a check for the first day of the month. If it's the 1st of the month, the formula displays the previous month (March) as "This Month." Otherwise, it follows the original logic of showing the current month.

Here’s the revised DAX formula for the CurrentMonth column:

CurrentMonth = 
VAR __current_date = UTCNOW() - (3 / 24)
VAR __is_first_of_month = DAY(__current_date) = 1
VAR __dif_month = DATEDIFF('Calendar'[Date], __current_date, MONTH)

RETURN
IF(
    __is_first_of_month, 
    IF(__dif_month = -1, "This Month", FORMAT('Calendar'[Date], "MMMM")),
    IF(__dif_month = 0, "This Month", FORMAT('Calendar'[Date], "MMMM"))
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa · Accepted solution]

Hi @danvrabie,

Thank you for reaching out to Microsoft Fabric Community.

The problem with the initial CurrentMonth Measure was that it directly compared the difference between each date in the Calendar table and the current date using DATEDIFF. When today is the 1st of the month, this comparison would return 0, indicating that it's still within the same month. But for your business logic, you actually wanted the previous month (March) to be labeled as "This Month" when it's the 1st of April, rather than showing April itself.
To address this, we revised the DAX by adding a check for the first day of the month. If it's the 1st of the month, the formula displays the previous month (March) as "This Month." Otherwise, it follows the original logic of showing the current month.

Here’s the revised DAX formula for the CurrentMonth column:

CurrentMonth = 
VAR __current_date = UTCNOW() - (3 / 24)
VAR __is_first_of_month = DAY(__current_date) = 1
VAR __dif_month = DATEDIFF('Calendar'[Date], __current_date, MONTH)

RETURN
IF(
    __is_first_of_month, 
    IF(__dif_month = -1, "This Month", FORMAT('Calendar'[Date], "MMMM")),
    IF(__dif_month = 0, "This Month", FORMAT('Calendar'[Date], "MMMM"))
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[danvrabie · Accepted solution]

Hi Anjan,

Thank you so much for the quick reply.

I have tested the proposed solution and had to make one change, where the first IF statement compares against a +1 and not -1 (most probably a typo).

I have also added an additional IF statement to not show future months in my slicer.

Below the amended solution, which meets all my requirements.

Once again, thank you for providing this solution, Anjan!

dan 

CurrentMonth =

VAR __current_date = UTCNOW() - (3 / 24)

VAR __is_first_of_month = DAY(__current_date) = 1

VAR __dif_month = DATEDIFF('Calendar'[Date], __current_date, MONTH)

RETURN

IF(

    __dif_month>-1,

    IF(

    __is_first_of_month,

    IF(__dif_month = 1, "This Month", FORMAT('Calendar'[Date], "MMMM")),

    IF(__dif_month = 0, "This Month", FORMAT('Calendar'[Date], "MMMM"))

))