Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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!
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!
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)
"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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |