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
Hey everyone! I could use some guidance on how to create a visual that is able to show each month (last year vs current year) side by side on a stacked or clusterted bar chart.
Desired Result:
2019 March
2020 March
2019 April
2020 April
2019 May
2020 May
Solved! Go to Solution.
Hi @Anonymous ,
Use if statement to make conditional judgment and calculate data of each month.
Measure =
IF (
(
YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( TODAY () )
|| YEAR ( MAX ( 'Table'[Date] ) )
= YEAR ( TODAY () ) - 1
)
&& MONTH ( MAX ( 'Table'[Date] ) ) <= MONTH ( TODAY () )
&& MONTH ( MAX ( 'Table'[Date] ) ) >= 3,
SUM ( 'Table'[Sales] )
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Use if statement to make conditional judgment and calculate data of each month.
Measure =
IF (
(
YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( TODAY () )
|| YEAR ( MAX ( 'Table'[Date] ) )
= YEAR ( TODAY () ) - 1
)
&& MONTH ( MAX ( 'Table'[Date] ) ) <= MONTH ( TODAY () )
&& MONTH ( MAX ( 'Table'[Date] ) ) >= 3,
SUM ( 'Table'[Sales] )
)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , You use last year trailing measure or datesmtd 1 year behind. All that with a date table. Move your mon-year in that table
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))))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
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/
@amitchandak thanks for the quick response!
I used the both formulas you provided but I am only seeing 2020, not both 2019/2020 side by side.
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))))
@Anonymous
Refer to this https://databear.com/power-bi-dax-sameperiodlastyear-paralellperiod-and-dateadd/
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
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 |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |