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

Use one slicer with multiple relationships to calendar table

Hi, 

 

Another problem I've been stuck with a while. 

 

I have a prognosis file for sales the rest of the year, and want to compare my prognosis for the rest of the year with the prognosis last month. I have a prognosis table with 4 fields in : Date for prognosis, Account, Date (for sales), Amount. In the real file I also have actuals so the prognosis for the entire year is comprised of Actual YTD + Prognosis rest of year.

 

What I would like to do is to use a slicer for YearMonth (a field in my calendar table), where I can select for example 201903 and get the results out like this: 

 

                               201901           201902           201903       201904      201905     201906    201907 etc.

Account 3000           3,576              5,413              6,214          7,580          8,555        3,287       2,574

 

In this example I would get the actuals for 201901 and 201902, and the prognosis for months between 201903 to 201912, and filtered where Date for prognosis = a date in March 2019. I also then want another measure for what it looked like last month where I had the actuals for 201901 and the prognosis for 201902-201912 where Date for prognosis = a date in February 2019. 

 

Can't get my head around how to do this. Is it possible to do it using only one slicer, or do I need to use two?

 

Example file in Excel is attached here: 

https://www.dropbox.com/s/7pd312txfksjkdc/Sample%20file%20for%20PowerBI%20forum.xlsx?dl=0 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

@Anonymous  - thinking about this some more...

I think the new date table should be disconnected (no relationship to the fact table). That way you can use a slicer to get a single value and then use that value for comparison, e.g. use forecast values after that date, and actuals before that date. This may help.

Cheers!

Nathan

View solution in original post

v-chuncz-msft
Community Support
Community Support

@Anonymous ,

 

You may take a look at the following posts.

Community Support Team _ Sam Zha
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

6 REPLIES 6
v-chuncz-msft
Community Support
Community Support

@Anonymous ,

 

You may take a look at the following posts.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@Anonymous - I would create a separate date table for prognosis date. Then you can use the selected date (or month) from your Prognosis Date to filter the rows in your fact table. 

Cheers!

Nathan

Anonymous
Not applicable

What I was aiming for was to get to use one slicer instead of two. It is not a big deal, but since I am convinced it is possible somehow I wanted to see what kind of solutions I could get. Like you say, I could use a separate date table where I use a slicer connected to that table 

Anonymous
Not applicable

@Anonymous  - thinking about this some more...

I think the new date table should be disconnected (no relationship to the fact table). That way you can use a slicer to get a single value and then use that value for comparison, e.g. use forecast values after that date, and actuals before that date. This may help.

Cheers!

Nathan

Anonymous
Not applicable

I ended up with two calendar tables, and two different slicers, one to slice actual data and one to slice the prognoses. 

Anonymous
Not applicable

Sorry, but haven't had the time to sit down and understand this model. It is a bit over my head at the moment, so have to spend some time understanding it. Will respond again when things at work have slowed down a bit and I have the time. Thanks for the help. 

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.