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.
Hi There, I have learnt a lot from the community. Thanks for that first. This is my first post & I have a query.
I have a bar chart which displays each month total. [Count of users]
My date filters are from the same table (its a Date Range with slider).
I need to show rolling 6 months data in each month bar.
Eg. If i select a date range in the filter (01-01-2021 to 30-06-2021) the following is expected
June 2021 bar should display Jan 2021 to May 2021 aggreagate data , and
May 2021 data bar should display December 2020 to April 2021 show aggreagate data and so on.
Every month bar should have the previous 6 months data in it.
I have tried writing a measure as follows
If i display this measure in a card and use the date filter as dropdown and select particular month and year this displays the last 6 months values from June 2021
Thanks in advance.
Note: I am a beginner 🙂
Solved! Go to Solution.
// First of all, a correct model in PBI
// NEVER uses the auto-generated date
// tables and date/time hierarchies. They
// are---simply put---wrong. That's the first thing.
// Second, in a good model, one where
// there's a proper Date table as outlined
// in, say, https://dax.guide/dateadd, you
// can write:
[User Count 6M-Sum] =
// Grab the last date visible in the
// current context
var LastVisibleDate = MAX( Dates[Date] )
// Create a new filter on Dates
// that spans a period of 6 months
// which ends on the LastVisibleDate.
var NewDateFilter =
DATESINPERIOD(
Dates[Date],
LastVisibleDate,
-6, MONTH
)
var Result =
// Apply the new filter and remove
// all original filters from Dates.
CALCULATE(
[User Count],
NewDateFilter,
REMOVEFILTERS( Dates )
)
return
Result
// First of all, a correct model in PBI
// NEVER uses the auto-generated date
// tables and date/time hierarchies. They
// are---simply put---wrong. That's the first thing.
// Second, in a good model, one where
// there's a proper Date table as outlined
// in, say, https://dax.guide/dateadd, you
// can write:
[User Count 6M-Sum] =
// Grab the last date visible in the
// current context
var LastVisibleDate = MAX( Dates[Date] )
// Create a new filter on Dates
// that spans a period of 6 months
// which ends on the LastVisibleDate.
var NewDateFilter =
DATESINPERIOD(
Dates[Date],
LastVisibleDate,
-6, MONTH
)
var Result =
// Apply the new filter and remove
// all original filters from Dates.
CALCULATE(
[User Count],
NewDateFilter,
REMOVEFILTERS( Dates )
)
return
Result
Thank a lot. That worked prefectly. 🤗
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |