Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I need to create the following report.
I choose a report month on a slicer on the first page (for example March 2023).
Then I need to display a clustered column chart for target clients for the previous 12 months (Apr 2022-Mar 2023). And also compare this data with the previous year. The visualisation should look like this:
What I did. I have 2 dim_dates tables: slicer is linked to report_dates table, fact table is linked with dim_dates table.
Created measures:
Gives me the end of the report period: EndDate = MAX('report_dates'[short_date])
Gives me the start of 1-year period: 1YearStartDate = EOMONTH([EndDate],-12)+1
Measure that counts target clients for the previous 12 month: Count Target1Y = CALCULATE(SUM('fact_table' [target_client]),KEEPFILTERS(DATESBETWEEN('dim_dates'[short_date],[1YearStartDate],[EndDate])))
'fact_table'[target_client]) is either 1 or 0.
Measure that counts target clients for the same period last year: Count Target1YLastYear =
VAR curPeriod = DATESBETWEEN('report_dates'[short_date],[1YearStartDate],[EndDate])
VAR prevPeriod = DATEADD(DATESBETWEEN('dim_dates'[short_date],[1YearStartDate],[EndDate]),-1,YEAR)
RETURN
CALCULATE(CALCULATE(SUM('fact_table'[target_client]),KEEPFILTERS(curPeriod)),KEEPFILTERS(prevPeriod))
But I have visualisation like this
I also tried the same table dim_dates in curPeriod and prevPeriod, but this does not work.
Could you please tell me how to resolve this task?
Many thanks
Solved! Go to Solution.
Here's one way to do it.
1. Make sure your 2nd date table is disconnected (no relationship)
2. Use the month column from your 1st date table in the visual and your 2nd date table in the slider.
3. Create two measures like this (the Date2 table is the one in the slicer).
Last 12 Mos CY =
VAR maxdate2 =
MAX ( 'Date2'[Date] )
VAR maxdate1 =
MAX ( 'Date'[Date] )
VAR mindate =
EOMONTH ( maxdate2, -12 ) + 1
RETURN
IF ( maxdate1 <= maxdate2 && maxdate1 >= mindate, [Total Qty] )
Last 12 Mos PY =
VAR maxdate2 =
MAX ( 'Date2'[Date] )
VAR maxdate1 =
MAX ( 'Date'[Date] )
VAR mindate =
EOMONTH ( maxdate2, -12 ) + 1
RETURN
IF (
maxdate1 <= maxdate2
&& maxdate1 >= mindate,
CALCULATE ( [Total Qty], DATEADD ( 'Date'[Date], -12, MONTH ) )
)
Pat
Here's one way to do it.
1. Make sure your 2nd date table is disconnected (no relationship)
2. Use the month column from your 1st date table in the visual and your 2nd date table in the slider.
3. Create two measures like this (the Date2 table is the one in the slicer).
Last 12 Mos CY =
VAR maxdate2 =
MAX ( 'Date2'[Date] )
VAR maxdate1 =
MAX ( 'Date'[Date] )
VAR mindate =
EOMONTH ( maxdate2, -12 ) + 1
RETURN
IF ( maxdate1 <= maxdate2 && maxdate1 >= mindate, [Total Qty] )
Last 12 Mos PY =
VAR maxdate2 =
MAX ( 'Date2'[Date] )
VAR maxdate1 =
MAX ( 'Date'[Date] )
VAR mindate =
EOMONTH ( maxdate2, -12 ) + 1
RETURN
IF (
maxdate1 <= maxdate2
&& maxdate1 >= mindate,
CALCULATE ( [Total Qty], DATEADD ( 'Date'[Date], -12, MONTH ) )
)
Pat
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
110 | |
107 | |
92 | |
68 |
User | Count |
---|---|
167 | |
130 | |
129 | |
94 | |
91 |