Guage visualization for Plan vs Actual

[Wmian]

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.

[v-zhangti · Accepted 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.

[KerKol]

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