View My Drafts
Hi Nishesh,I have a few pointers for you here.1. The first thing that I would do is to sort out the table with the Scenario column. I will call this table 'Facts'. The Facts[Scenario] column is good. Keep it that way. What I would change is the Facts[Period] column and remove all the aggregation rows like YTD and FY. Then add a Facts[Date] column that is most likely the last date of each month (if monthly numbers are the most fine-grained in your dataset). I have written an article about this here where I call the equivalent table 'Journals' ABC of Power BI: 'J' is for Journals | AccountingWEB .2. The next thing that I would do is to create a 'Dates' dimension table with a 1:Many relationship from Dates[Date] to Facts[Date]. Here is an article that I wrote about the power of filtering dates in Power BI, with links to further articles and videos on the subject. ABC of Power BI: 'D' is for Date | AccountingWEB3. Finally, the simplest approach, I believe, would be to create three Measures [Actual], [Budget] and [Forecast]. You can do this using Calculate. For YTD etc., personally I would avoid using the DAX Time Intelligence functions, as I mention in the article.These three steps will get your basic setup. Beyond this, you can add many more bells and whistles to make the report more intuitive and interactive, but getting the data model correct in the first place as I outline here is where I would start. I hope this is helpful.Hugh
What you can do is just create dax formula to separate the 3 scenarios then calculate the variance e.g. Actual = Calculate(Sum(value),scenario = "Actual").
------------------------------Ardie TrinidadSenior Finance Analyst------------------------------