cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
theyk
Regular Visitor

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? 

1 ACCEPTED SOLUTION
YukiK
Responsive Resident
Responsive Resident

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!

View solution in original post

5 REPLIES 5
YukiK
Responsive Resident
Responsive Resident

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
Regular Visitor

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
Responsive Resident
Responsive Resident

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!

View solution in original post

theyk
Regular Visitor

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
Responsive Resident
Responsive Resident

"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):

YukiK_1-1634825216058.png

 

If this helps, please give it a thums up!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors