Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am looking for some ideas/help in devloping a dashboard where my data is in more than 48 columns (for 4 years by month) as planned.
I will have actaul data as well as data becomes available in future months.
I want have guage visualization with slicers e.g. Year, Month, Cost Type & Categories.
I would like to see when 2022 is selected in slicer it shows plan from Dec and actual from current month
if I further click June in month, I would like to see June data in plan and actual
I am bit confuse how to start this dashboard.
I am new to PBI with success of 5 reports published.
Thanks in advance.
Solved! Go to Solution.
Hi, @Wmian
You can try the following methods.
Create a new date table.
Date = CALENDAR(DATE(2019,1,1),DATE(2022,12,31))
Year = YEAR([Date])
Month = MONTH([Date])
I simulated some of the data. Create a relationship between the two tables with date.
The output of actual is represented by measure.
Actual M =
IF (
SELECTEDVALUE ( 'Date'[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( 'Date'[Month] ) = BLANK (),
CALCULATE (
SUM ( 'Table'[actual] ),
FILTER ( ALLSELECTED ( 'Date' ), [Month] = MONTH ( TODAY () ) )
),
IF (
SELECTEDVALUE ( 'Date'[Month] ) <> BLANK (),
CALCULATE (
SUM ( 'Table'[actual] ),
FILTER ( ALLSELECTED ( 'Date' ), [Month] = SELECTEDVALUE ( 'Date'[Month] ) )
)
)
)
Does this match the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Wmian
You can try the following methods.
Create a new date table.
Date = CALENDAR(DATE(2019,1,1),DATE(2022,12,31))
Year = YEAR([Date])
Month = MONTH([Date])
I simulated some of the data. Create a relationship between the two tables with date.
The output of actual is represented by measure.
Actual M =
IF (
SELECTEDVALUE ( 'Date'[Year] ) = YEAR ( TODAY () )
&& SELECTEDVALUE ( 'Date'[Month] ) = BLANK (),
CALCULATE (
SUM ( 'Table'[actual] ),
FILTER ( ALLSELECTED ( 'Date' ), [Month] = MONTH ( TODAY () ) )
),
IF (
SELECTEDVALUE ( 'Date'[Month] ) <> BLANK (),
CALCULATE (
SUM ( 'Table'[actual] ),
FILTER ( ALLSELECTED ( 'Date' ), [Month] = SELECTEDVALUE ( 'Date'[Month] ) )
)
)
)
Does this match the output you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You might find it more useful to have a single Date Field and a Date Table to be able to filter and slice and dice your data.
This might get you started:
Learn why you need to UNPIVOT your data and then PIVOT it back - Power BI - YouTube
Also Data Modelling In Power BI: Tips & Best Practices - Enterprise DNA
Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD
Proud to be a Super User!
User | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |