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.
Hello,
I would like to make two bars (in one visual so that they can have the same axis). One is values for previous YearMonth, another is for selected YearMonth. Also, is it possible to add a filter MTD/YTD?
For example,
when I select 201507, and select YTD, one bar will be the sum value of 201401~201407, and another bar will be 201501~201507.
when I select 201507, and select MTD, one bar will be the sum value of 201407, and another bar will be 201507.
I attached pbix file in onedrive. https://1drv.ms/u/s!ApmbqlXBl3vGgQ_ybCdD9qNmsQjR
Also, if the above can achieve, may be the following table can also realize?(e.g.when select 201507)
Thanks a lot!
Solved! Go to Solution.
Hi @YunJ ,
I have created a sample for your reference.
Measure =
VAR sel =
SELECTEDVALUE ( 'date'[Yearmonth] )
VAR pre =
( YEAR ( MAX ( 'date'[Date] ) ) - 1 ) * 100
+ MONTH ( MAX ( 'date'[Date] ) )
RETURN
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
'Table',
'Table'[date] IN VALUES ( 'date'[Date] )
|| YEAR ( 'Table'[date] ) * 100
+ MONTH ( 'Table'[date] ) = pre
)
)
Measure 2 =
VAR m =
MONTH ( MAX ( 'date'[Date] ) )
VAR k =
FILTER ( 'Table', 'Table'[Month] <= m )
RETURN
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[year] ),
'Table'[Month] <= MAX ( 'Table'[Month] )
),
KEEPFILTERS ( k )
)
Pbix as attached.
Hi @YunJ ,
I have created a sample for your reference.
Measure =
VAR sel =
SELECTEDVALUE ( 'date'[Yearmonth] )
VAR pre =
( YEAR ( MAX ( 'date'[Date] ) ) - 1 ) * 100
+ MONTH ( MAX ( 'date'[Date] ) )
RETURN
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
'Table',
'Table'[date] IN VALUES ( 'date'[Date] )
|| YEAR ( 'Table'[date] ) * 100
+ MONTH ( 'Table'[date] ) = pre
)
)
Measure 2 =
VAR m =
MONTH ( MAX ( 'date'[Date] ) )
VAR k =
FILTER ( 'Table', 'Table'[Month] <= m )
RETURN
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[year] ),
'Table'[Month] <= MAX ( 'Table'[Month] )
),
KEEPFILTERS ( k )
)
Pbix as attached.
Hello Frank,
Thanks for your solution. I'm a little new to power bi. May I ask some questions about what you did?
For this Measure, I'm wondering
For this measure2,
why use ALLEXCEPT and KEEPFILTERS? I created another Measure3 only used FILTER and have the same result.
Thanks a lot for your patience.
Yun
You can use time intelligence with date calendar. Have the month year format there
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year + 3 week behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(dateadd('Date'[Date],-1,Year),-21,Day))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
last QTR same Month (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |