Current Month Filter With Special Case for the First Day of the Month

DAX DAX Calculations
1

Hi,
I want to filter a visual to always show the current month.

However, on the first day of the current month no data for this month has been loaded as yet. The data load takes place at the end of the Day 1 and is then refreshed overnight. In this instance I would like to keep showing the previous months data until Day 2 of the current month.

Is there anyway that I can achieve this using DAX? Any advice would be very helpful!

2

Hi @Kazza,

It will be help-full if you attach the pbix file to get faster response.

3

Sure. I shall do that.

4

Hi @Kazza,

You can try to use below DAX and see if you can achieve what you are looking for.
NOTE:- You should have a separate Date Table (reference at the end).

Display = 
VAR current_month =
    MONTH ( TODAY () )
VAR current_month_sales =
    CALCULATE (
        SUM ( 'Order Line'[AMOUNT_DUE_REMAINING] ),
        FILTER ( 'Date (2)', MONTH ( 'Date (2)'[MonthOfYear] ) = current_month )
    )
VAR last_month_sales =
    CALCULATE (
        SUM ( 'Order Line'[AMOUNT_DUE_REMAINING] ),
        FILTER ( 'Date (2)', MONTH ( 'Date (2)'[MonthOfYear] ) = current_month - 1 )
    )
RETURN
    IF ( 
        DAY ( TODAY () ) = 1,
        last_month_sales, 
        current_month_sales 
    )

Date (2) is the date Table.
‘Order Line’[AMOUNT_DUE_REMAINING] is the table with column where you want to get sum of.

https://forum.enterprisedna.co/t/creating-a-date-table/5954

5

Thanks so much Melissa, however, I wanted to use it as a filter on a table visual to filter the dates and in the case of day 1 for it to show all the dates from last month.
I shall look at what you have provided and see if I can adapt it.

Thank you again!

6

Hi @Kazza

As suggested please provide your pbix along with the desired result. So that we can try to provide the solution.

1 Like
7

Create a calculated column in your date table using.

Dates[Date] <= EOMONTH(TODAY(),-1)+2

And then use this column in the filters pane to filter only TRUE values.

8

Thanks so much I think that may work. I have learnt quite a lot from both suggestions Melissa and Antrik, thank you.

So thanks EnterpriseDNA!