Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Bar Chart visual forecasting (Planned budget vs actual revenue)

Hi there,

 

I have an issue with a visual in my power bi report. the scenario is as follow:

 

I am trying to build a page with different visuals including a bar chart with 2 values:

- Actual revenue

- Planned revenue

 

I have a date table starting from 1-1-2018.

my date slicer is 1-1-2019 --> 31-7-2019

I am using a filter to eliminate all dates after today date ( Date[date] <=TodayDate = True)

 

What I want to visualize:

 

- Actual revenue = only data based on selected slicer date values

- Planned revenue = Selected Slicer date values + 6 months (Dynamics = if i increase MAX or Decrease slicer date, it should be always +6 months)

 

I have tried to apply different measures and columns but still not succeeded.

 

Note that 1 page contains different visuals and If i delete any filter it will have impact on all visuals not only planned revenue bar chart. 

 

Any tips?

 

Thank you 🙂

 

Regards,

 

Hicham

2 ACCEPTED SOLUTIONS
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Create a calendar table with relationship with your table (called Table in my test)

calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"year-month",FORMAT([Date],"yyyymm"))

Create another calendar table without any relationship with other tables.

filter calendar = 'calendar'

set the interactions for visuals:Format->Edit interactions

for the slicer from calendar table

Capture1.JPG

For the slicer from filter calendar table

Capture2.JPG

Create measures in Table

m =
VAR mindate =
    MIN ( 'filter calendar'[Date] )
VAR maxdate =
    MAX ( 'filter calendar'[Date] )
RETURN
    IF (
        MAX ( 'calendar'[Date] ) <= maxdate
            && MAX ( 'calendar'[Date] ) >= mindate,
        1,
        0
    )

selected actual value = CALCULATE(SUM('Table'[actual value]),FILTER('Table','Table'[m]=1))

selected+6 plan value =
VAR maxdate =
    MAX ( 'filter calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[plan value] ),
        FILTER (
            'Table',
            [m] = 1
                || (
                    DATEDIFF ( maxdate, 'Table'[date], MONTH ) >= 1
                        && DATEDIFF ( maxdate, 'Table'[date], MONTH ) <= 6
                )
        )
    )

Capture3.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @Anonymous 

I create a "filter calendar table", use the date from this table into a slicer, use this slicer to control +6months visual.

As the "filter calendar table" has no reltionship with any other tables in your pbix, so this slicer won't filter any visual except the +6months visual.

You could edit the slicer name to let users know this a specific slicer.

 

Then to filter date for other visuals, you could use "date" from "calendar" table, just edit the interaction for the slicer and +6months visual as "ignore".

 

Thus, you have two date slicers on a page of the reports.

One slicer from "calendar table" filters all other visuals except 6months visual.

Another from "filter calendar table", only filter 6months visual.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Create a calendar table with relationship with your table (called Table in my test)

calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"year-month",FORMAT([Date],"yyyymm"))

Create another calendar table without any relationship with other tables.

filter calendar = 'calendar'

set the interactions for visuals:Format->Edit interactions

for the slicer from calendar table

Capture1.JPG

For the slicer from filter calendar table

Capture2.JPG

Create measures in Table

m =
VAR mindate =
    MIN ( 'filter calendar'[Date] )
VAR maxdate =
    MAX ( 'filter calendar'[Date] )
RETURN
    IF (
        MAX ( 'calendar'[Date] ) <= maxdate
            && MAX ( 'calendar'[Date] ) >= mindate,
        1,
        0
    )

selected actual value = CALCULATE(SUM('Table'[actual value]),FILTER('Table','Table'[m]=1))

selected+6 plan value =
VAR maxdate =
    MAX ( 'filter calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[plan value] ),
        FILTER (
            'Table',
            [m] = 1
                || (
                    DATEDIFF ( maxdate, 'Table'[date], MONTH ) >= 1
                        && DATEDIFF ( maxdate, 'Table'[date], MONTH ) <= 6
                )
        )
    )

Capture3.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi @v-juanli-msft 

 

Thank you very much for your effort.

 

Your solution works well. 

 

I have still just one matter, implementing your solution leads to not being able to use the normal date table as a filter in all my 10 report pages. I have applied the date filter in the first and it is synergized with all pages.

 

I am using the Date Calendar filter to control +6months visual. I want to avoid using 2 different date filters in every page. IT should work dynamically for all pages, is that possible to arrange?

Hi @Anonymous 

I create a "filter calendar table", use the date from this table into a slicer, use this slicer to control +6months visual.

As the "filter calendar table" has no reltionship with any other tables in your pbix, so this slicer won't filter any visual except the +6months visual.

You could edit the slicer name to let users know this a specific slicer.

 

Then to filter date for other visuals, you could use "date" from "calendar" table, just edit the interaction for the slicer and +6months visual as "ignore".

 

Thus, you have two date slicers on a page of the reports.

One slicer from "calendar table" filters all other visuals except 6months visual.

Another from "filter calendar table", only filter 6months visual.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.