Forum Discussion
Create Monthly reports with Dynamic dateTime filter
I have an orders dataset, where I have the following fields
order_datetime
order_id
settlement_datetime
amount
country
order_type
I wanted to create a subscription so that on 15th of every month, it generates a report for the previous month ( 1st of previous month to 1st of current month ). But the problem I have is, I don't want any human intervention, changing the order_datetime filter every month instead be dynamic.
For example:
On 15th of Oct, I would be needing the orders report from 1st Sept to 1st Oct ( filter on order_datetime ) . So every nth month, it should generate a report of (n-1)th month. Can someone advise me what I should use?
Then this should do it:
Your table =
VAR __FirstDayOfThisMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
VAR __FirstDayOfLastMonth = EDATE ( DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 1)RETURN
FILTER (
financials,
AND (
financials[Date] >= __FirstDayOfLastMonth,
financials[Date] <= __FirstDayOfThisMonth
)
)Hope that helps!
5 Replies
This DAX returns a table with last month's data. If today is 2021-10-19 then it'll return the data from 2021-9-19 to 2021-10-19. Make sure you refresh the data source every day so that the table will update accordingly.
FILTER ( financials, financials[Date] >= EDATE ( TODAY() , 1 ) )
If you find this helpful, please give it a thums up!
The ask was different :D. Wanted to get the data for the range between 1st of previous month and 1st of the current month.
Then this should do it:
Your table =
VAR __FirstDayOfThisMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
VAR __FirstDayOfLastMonth = EDATE ( DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 1)RETURN
FILTER (
financials,
AND (
financials[Date] >= __FirstDayOfLastMonth,
financials[Date] <= __FirstDayOfThisMonth
)
)Hope that helps!
