Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
@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 ,
You may take a look at the following posts.
@Anonymous ,
You may take a look at the following posts.
@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
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 - 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
I ended up with two calendar tables, and two different slicers, one to slice actual data and one to slice the prognoses.
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.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |