Create Monthly reports with Dynamic dateTime filter

[theyk]

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? 

[YukiK]

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!

[theyk]

The ask was different :D. Wanted to get the data for the range between 1st of previous month and 1st of the current month. 

[YukiK · Accepted solution]

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!

[theyk]

cool. Have a follow-up question,  Let's say I refresh data every 15 days the newly filtered table will also be updated right? 

Also, Taking this opportunity, I would like to subtract the timeZone offset for __FirstDayOfThisMonth & __FirstDayOfLastMonth. For the offset mapping I have created a table (named timeZone)  that has the columns country_code and offset  . 

This is how the table looks like:

SG  8

IN   5.5

I can get the country_code from parameters or even hardcode should not be a problem . Is there a way I get the offset by using second table

Eg: I want to do something like this

VAR timeZoneOffset =  timeZoneTable['SG']

VAR __FirstDayOfThisMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - duration(0,timeZoneOffset,0,0)

[YukiK]

"Have a follow-up question, Let's say I refresh data every 15 days the newly filtered table will also be updated right? " -> Yes. Calculcated tables get updated every time the data model refreshes.

You may be able to do that using duration() but here is a simpler version to get the result (this is a calculated column):

If this helps, please give it a thums up!