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 base table like below which stores income of my organisation. This table contains data from 2017 onwards.
One of my BA requested report like below where he will select any two consecutive Month_Year and output should be like below.
Above output is shown when Jan-20 and Feb-20 Month_Year are selected.
What I have done as of now is that, in the Matrix visual, I have put Month-Year field in the Column part. But how do I achieve dynamic Month level calculation ?
Can someone please help me with it @Greg_Deckler
Solved! Go to Solution.
Hi @Anonymous ,
If you mean the logic of %Diff should be (4000-6000)/4000 = -33%, we can try to use the following steps to meet your requirement:
1. create a calculated table:
Column Header Table =
var startDate = MIN('Table'[Date])
var endDate = MAX('Table'[Date])
var t = FILTER(CALENDAR(DATE(YEAR(startDate),MONTH(startDate),1),DATE(YEAR(endDate),MONTH(endDate)+1,1)-1),DAY([Date])=1)
return
ADDCOLUMNS(CROSSJOIN(DISTINCT('Table'[Component]),UNION(ADDCOLUMNS(t,"Month-Year",FORMAT([Date],"MMM-YYYY")),ADDCOLUMNS(t,"Month-Year","% Diff"))),"Sort Column",SWITCH([Month-Year],"% Diff",COUNTROWS(t)+1,RANKX(t,[Date],,ASC,Dense)))
2. sort "Month-Year" column by "Sort Column"
3. create a measure and use it in matrix visual:
Value Measure =
VAR t =
FILTER (
ALLSELECTED ( 'Column Header Table' ),
'Column Header Table'[Component]
IN DISTINCT ( 'Column Header Table'[Component] )
)
RETURN
IF (
ISINSCOPE ( 'Column Header Table'[Month-Year] ),
SWITCH (
SELECTEDVALUE ( 'Column Header Table'[Month-Year] ),
"% Diff",
VAR maxMonth =
CALCULATETABLE (
DISTINCT ( 'Column Header Table'[Month-Year] ),
FILTER (
t,
'Column Header Table'[Sort Column]
= CALCULATE (
MAX ( 'Column Header Table'[Sort Column] ),
FILTER ( t, 'Column Header Table'[Month-Year] <> "% Diff" )
)
)
)
VAR minMonth =
CALCULATETABLE (
DISTINCT ( 'Column Header Table'[Month-Year] ),
FILTER (
t,
'Column Header Table'[Sort Column]
= CALCULATE ( MIN ( 'Column Header Table'[Sort Column] ), t )
)
)
VAR valueInMaxMonth =
CALCULATE (
SUM ( 'Table'[Amount Received] ),
FILTER (
'Table',
'Table'[Component] IN DISTINCT ( 'Column Header Table'[Component] )
&& 'Table'[Month_Year] IN maxMonth
)
)
VAR valueInMinMonth =
CALCULATE (
SUM ( 'Table'[Amount Received] ),
FILTER (
'Table',
'Table'[Component] IN DISTINCT ( 'Column Header Table'[Component] )
&& 'Table'[Month_Year] IN minMonth
)
)
RETURN
FORMAT ( valueInMaxMonth / valueInMinMonth - 1, "Percent" ),
""
& CALCULATE (
SUM ( 'Table'[Amount Received] ),
FILTER (
'Table',
'Table'[Component] IN DISTINCT ( 'Column Header Table'[Component] )
&& 'Table'[Month_Year] IN DISTINCT ( 'Column Header Table'[Month-Year] )
)
)
),
CALCULATE (
SUM ( 'Table'[Amount Received] ),
'Table'[Component] IN DISTINCT ( 'Table'[Component] )
)
)
Best regards,
Hi @Anonymous ,
If you mean the logic of %Diff should be (4000-6000)/4000 = -33%, we can try to use the following steps to meet your requirement:
1. create a calculated table:
Column Header Table =
var startDate = MIN('Table'[Date])
var endDate = MAX('Table'[Date])
var t = FILTER(CALENDAR(DATE(YEAR(startDate),MONTH(startDate),1),DATE(YEAR(endDate),MONTH(endDate)+1,1)-1),DAY([Date])=1)
return
ADDCOLUMNS(CROSSJOIN(DISTINCT('Table'[Component]),UNION(ADDCOLUMNS(t,"Month-Year",FORMAT([Date],"MMM-YYYY")),ADDCOLUMNS(t,"Month-Year","% Diff"))),"Sort Column",SWITCH([Month-Year],"% Diff",COUNTROWS(t)+1,RANKX(t,[Date],,ASC,Dense)))
2. sort "Month-Year" column by "Sort Column"
3. create a measure and use it in matrix visual:
Value Measure =
VAR t =
FILTER (
ALLSELECTED ( 'Column Header Table' ),
'Column Header Table'[Component]
IN DISTINCT ( 'Column Header Table'[Component] )
)
RETURN
IF (
ISINSCOPE ( 'Column Header Table'[Month-Year] ),
SWITCH (
SELECTEDVALUE ( 'Column Header Table'[Month-Year] ),
"% Diff",
VAR maxMonth =
CALCULATETABLE (
DISTINCT ( 'Column Header Table'[Month-Year] ),
FILTER (
t,
'Column Header Table'[Sort Column]
= CALCULATE (
MAX ( 'Column Header Table'[Sort Column] ),
FILTER ( t, 'Column Header Table'[Month-Year] <> "% Diff" )
)
)
)
VAR minMonth =
CALCULATETABLE (
DISTINCT ( 'Column Header Table'[Month-Year] ),
FILTER (
t,
'Column Header Table'[Sort Column]
= CALCULATE ( MIN ( 'Column Header Table'[Sort Column] ), t )
)
)
VAR valueInMaxMonth =
CALCULATE (
SUM ( 'Table'[Amount Received] ),
FILTER (
'Table',
'Table'[Component] IN DISTINCT ( 'Column Header Table'[Component] )
&& 'Table'[Month_Year] IN maxMonth
)
)
VAR valueInMinMonth =
CALCULATE (
SUM ( 'Table'[Amount Received] ),
FILTER (
'Table',
'Table'[Component] IN DISTINCT ( 'Column Header Table'[Component] )
&& 'Table'[Month_Year] IN minMonth
)
)
RETURN
FORMAT ( valueInMaxMonth / valueInMinMonth - 1, "Percent" ),
""
& CALCULATE (
SUM ( 'Table'[Amount Received] ),
FILTER (
'Table',
'Table'[Component] IN DISTINCT ( 'Column Header Table'[Component] )
&& 'Table'[Month_Year] IN DISTINCT ( 'Column Header Table'[Month-Year] )
)
)
),
CALCULATE (
SUM ( 'Table'[Amount Received] ),
'Table'[Component] IN DISTINCT ( 'Table'[Component] )
)
)
Best regards,
With date calendar, you can use totalmtd or dates mtd and change % on that
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
diff %=divide([MTD Sales ],[last MTD Sales]) -1
Hi @amitchandak , the given formula produces blank output on the visual. Below are my formulas. Please let me know if I have missed anything.
In case your date dimension is not stooping at the current month and you are not using a filter from date dimension. The MTD formula will run from the calendar end date.
So have date filter or use a relative date filter at page or visual level.
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 |
---|---|
115 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |