Automatically filter month based on real date

[Nerdywantocode]

Hi guys, I'm currently an finance officer and on of my monthly task is to create a PBI dashboard to present data of company. I want to automate my report so that once I have new data, I just have to refresh and PBI will do everything. Currently, I have a trouble with my task.

My data contain transactions of all month, spread year to year. For the purpose of the report, I only extract two most recent months for calculation and visualization. Data will look like this ( assume name of this table below is Data):

Date	Transactions
17-10-23	A
18-10-23	B
19-10-23	C
16-11-23	D
15-11-23	E
19-11-23	F
24-12-23	G
12-12-23	H
23-12-23	I

For example, in December, I only take transactions of November and October for reporting. I create a new calculated column name Month, in which is the month number of column date. Then I create a new table with the formula:

Data_report = Filter(Data, Data[Month]=Month(today())||Data[Month] = Month(today())-1)

With this DAX, each time I have to do monthly report, data will be selected for the most 2 recent months.

Usually, this DAX runs well. However, since it is now Jannuary, this dax no longer runs. I realized the problem is my Dax now calculate results will be 0 and -1, not 11 or 12 as I expected. 

Thus, anyone can help me to fix my DAX or create a new one to automate the data processing.

Sorry English is not my first language, so my post is difficult to understand, please leave me a message.

Many thanks

Regards,

[jdbuchanan71 · Accepted solution]

@Nerdywantocode 

You should be able to do it just using the dates without adding the month to the table like this.

Data_report = 
VAR _Start = EOMONTH(TODAY(),-2)+1
VAR _End = EOMONTH(TODAY(),0)
RETURN 
FILTER ( Data, Data[Date] >= _Start && Data[Date] <= _End )

[v-heq-msft · Accepted solution]

Hi @Nerdywantocode ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

1.Create a new calculated column in  `Data` table to determine the year and month combination for each transaction date.

YearMonth = YEAR(Data[Date]) * 100 + MONTH(Data[Date])

2.Create a measure.

Two months ago = IF(
    MONTH(TODAY()) = 1 || 2,
    (YEAR(TODAY()) - 1) * 100 + 11,
    YEAR(TODAY()) * 100 + MONTH(TODAY()) - 2
)

3.Create another measure to calculate the  year and last month combination.

LastMonth = IF(
    MONTH(TODAY()) = 1 || 2,
    (YEAR(TODAY()) - 1) * 100 + 12,
    YEAR(TODAY()) * 100 + MONTH(TODAY()) - 1
)

4.Create a calculate table to show the final output

Table = CALCULATETABLE(Data,
    FILTER(
    Data,
    Data[YearMonth] = [Two months ago] || Data[YearMonth] = [LastMonth])
)

5.Final output

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jdbuchanan71 · Accepted solution]

@Nerdywantocode 

You should be able to do it just using the dates without adding the month to the table like this.

Data_report = 
VAR _Start = EOMONTH(TODAY(),-2)+1
VAR _End = EOMONTH(TODAY(),0)
RETURN 
FILTER ( Data, Data[Date] >= _Start && Data[Date] <= _End )

[Nerdywantocode]

Thank you. I will try this later with my data in the company tmr