Forum Discussion
Helper ITotal next to year to date value
Hi Plantje
It took times for me to understand but here it is :
Actuals Total = CALCULATE(SUM(Table1[Actuals]);ALLEXCEPT(Table1;Table1[AccountNumber];Table1[Fiscal year])) Budget Total = CALCULATE(SUM(Table1[Budget]);ALLEXCEPT(Table1;Table1[AccountNumber];Table1[Fiscal year]))
Maybe you will have to replace ; by , (depend of countries)
- Quentin
Solution Sage
What do you mean when you say you can't figure it out ? You don't know where it come from ?
plus I can't create a table in PowerBI in that way, I need you to copy and paste the data here and not the query
Thanks
- Quentin
Helper IAh okay, I'm sorry... I'm using a SQL server source. I'll try to paste the data as a table. I'll read through the link that you gave once more. I will give that proposed way a try as soon as I am back at my desk.
What I cannot figure out is how to get the values Budget Total and Actuals Total as I described in my example.
- quentin_vigne
Where did your example come from ?
If you don't know the calculation for these columns it makes the thing a little bit harder
- Quentin
- Plantje
I have calculated the example myself. The data shown in the screen shots is all the data that I worked with for reproducing the issue I am running into. The example is as easy as summing the values that I need. The only thing is: I don't know how to get Power BI to do that.
- Plantje
Accountnumber Fiscal year Fiscal month Book date Actuals Budget
123 2018 1 1-1-2018 00:00:00 12 10 123 2018 2 1-2-2018 00:00:00 12 10 123 2018 3 1-3-2018 00:00:00 12 10 123 2017 12 1-12-2017 00:00:00 14 11 123 2017 11 1-11-2017 00:00:00 14 11 456 2018 1 1-1-2018 00:00:00 2 4 456 2018 2 1-2-2018 00:00:00 3 5 456 2018 3 1-3-2018 00:00:00 2 4 456 2018 4 1-4-2018 00:00:00 2 4 123 2017 1 1-1-2017 00:00:00 2 3 123 2017 2 1-2-2017 00:00:00 2 4 123 2017 3 1-3-2017 00:00:00 7 8 That's the main data.
And this is part of my time dimension:
Datum Dag Maand Eerste dag van maand Maandnaam Week ISOweek Dag van week Kwartaal Jaar Eerste dag van jaar Style112 Style101
1-1-2018 00:00:00 1 1 1-1-2018 00:00:00 januari 1 1 2 1 2018 1-1-2018 00:00:00 20180101 01/01/2018 2-1-2018 00:00:00 2 1 1-1-2018 00:00:00 januari 1 1 3 1 2018 1-1-2018 00:00:00 20180102 01/02/2018 3-1-2018 00:00:00 3 1 1-1-2018 00:00:00 januari 1 1 4 1 2018 1-1-2018 00:00:00 20180103 01/03/2018 4-1-2018 00:00:00 4 1 1-1-2018 00:00:00 januari 1 1 5 1 2018 1-1-2018 00:00:00 20180104 01/04/2018 5-1-2018 00:00:00 5 1 1-1-2018 00:00:00 januari 1 1 6 1 2018 1-1-2018 00:00:00 20180105 01/05/2018 6-1-2018 00:00:00 6 1 1-1-2018 00:00:00 januari 1 1 7 1 2018 1-1-2018 00:00:00 20180106 01/06/2018 7-1-2018 00:00:00 7 1 1-1-2018 00:00:00 januari 2 1 1 1 2018 1-1-2018 00:00:00 20180107 01/07/2018 8-1-2018 00:00:00 8 1 1-1-2018 00:00:00 januari 2 2 2 1 2018 1-1-2018 00:00:00 20180108 01/08/2018 9-1-2018 00:00:00 9 1 1-1-2018 00:00:00 januari 2 2 3 1 2018 1-1-2018 00:00:00 20180109 01/09/2018