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,
I have a Date table as fact table and a few other dimension tables related to it with Date column. I have to create multiple visuals. I want to select one month (for instance Nov 2019) in "Filters on all page" and want some visual to show data for last 3 months (i.e. Sep, Oct, Nov), some to show data for 6 months and so on. I don't know how to do it. I am very new to Power BI.
PS - I have reports working if the respective date selection is made on their page. I want to make the date selection common to all the pages even for those visuals which are showing data for past 3/6/12 months.
Thanks in advance. 🙂
Saurabh
hi,
According to your description, please try these measures.
1) Calculate data for last 3 months
Measure =
VAR las3 =
EDATE ( MAX ( 'Table18'[Date] ), -3 )
VAR sell3 =
YEAR ( las3 ) * 100
+ MONTH ( las3 )
VAR sele =
YEAR ( MAX ( Table18[Date] ) ) * 100
+ MONTH ( MAX ( Table18[Date] ) )
VAR cal =
YEAR ( MAX ( Table11[Date] ) ) * 100
+ MONTH ( MAX ( 'Table11'[Date] ) )
RETURN
IF ( cal <= sele && cal > sell3, 1, 0 )
Sales-Test = CALCULATE(SUM(Table11[Sales]),FILTER(Table11,[Measure]=1))
And it shows as follows.
2) Calculate data for one month you select
Measure 3 =
var a = MONTH(MAX(Table11[Date]))
var b = MONTH(MAX(Table18[Date]))
return
IF(a=b,1,0)
Sales-Common = CALCULATE(SUM(Table11[Sales]),FILTER(Table11,[Measure 3]=1))
And it shows as follows.
Here is my test pbix file.
If you still have questions about this, please for free to let me know.
Best Regards,
Giotto Zhi
@v-gizhi-msft
Thank you very much for the reply.
But This is not what I want. I am able to solve the problem when there is just one table connected to the date table. Issue arises when multiple tables are used in the same visual. Please refer to the PBIX file.
Looking forward to your reply.
PS - I was thinking if we can use a staging Date table to use in slicer/filter for user selection input which is not related to any table and then somehow if we can pass that selection to the main Date table and use that there to create 3 dynamic conditional columns each for 1Month, 3 Months & 6 Months with just "Y" & "N" in them and thereafter we can use those conditional columns in filter pane to filter out original Date table accordingly?
hi,
According to your description, I create two tables to test:
Please try these measures:
Last3Months =
VAR selecteddate =
CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR l =
EOMONTH ( CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ), -2 )
RETURN
CALCULATE (
SUM ( Fact[Sales] ),
FILTER (
ALL ( 'Fact' ),
'Fact'[Date] <= selecteddate
&& 'Fact'[Date] >= DATE ( YEAR ( l ), MONTH ( l ), 1 )
)
)
Last6Months =
VAR selecteddate =
CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR l =
EOMONTH ( CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ), -5 )
RETURN
CALCULATE (
SUM ( Fact[Sales] ),
FILTER (
ALL ( 'Fact' ),
'Fact'[Date] <= selecteddate
&& 'Fact'[Date] >= DATE ( YEAR ( l ), MONTH ( l ), 1 )
)
)
Last12Months =
VAR selecteddate =
CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR l =
EOMONTH ( CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ), -11 )
RETURN
CALCULATE (
SUM ( Fact[Sales] ),
FILTER (
ALL ( 'Fact' ),
'Fact'[Date] <= selecteddate
&& 'Fact'[Date] >= DATE ( YEAR ( l ), MONTH ( l ), 1 )
)
)
By ‘Date’ slicer, it shows:
By this way, when you choose one date, the three visuals will display different data order by last 3/6/12 months.
It should be noticed that this way can only show the sum of last 3/6/12 months instead of detailed data of each month.
Here is my test pbix file.
If you still have questions about this, please for free to let me know.
Best Regards,
Giotto Zhi
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |