# PowerPivot Income Statement MTD & YTD



## anteus (Jun 29, 2017)

Hello Power BI forum!
I am trying to develop a PowerPivot based Income Statement report (my previous version is a Power Query table with SUMIFS but that has become extremely slow with 20k records of data - this is mistery to me too) and I cannot figure out how to filter YTD periods with slicer. Let me show the desired structure below:

                        Month Actuals / Month Budget / Month LastYear / YTD Actuals / YTD Budget / YTD LastYear
Revenues
Direct costs
...
...
...

Since the structure is fixed I would go with Cube formulas on the data model but don't know how to deal with YTD with the aim to be able to select any period in the past. The data records contain period, amount, account mapping info, etc.
Any help, hint, idea is appreciated.
Best regards, Laszlo


----------



## gazpage (Jun 29, 2017)

What would be the options in the slicer and what would you expect the output to be based on selection of those options?


----------



## akice (Jun 29, 2017)

I have done the same thing - SOO and Balance sheet in Excel/Power Pivot using cubes.  What i did was have a disconnect "parameter" table with years and months.  Make your selections with the slicers and then use the values in your measures to drive what period to do the YTD formulas through. Works just fine.


----------



## anteus (Jun 30, 2017)

gazpage said:


> What would be the options in the slicer and what would you expect the output to be based on selection of those options?



Ignoring the other filters I want to be able to select one month (any month in the past) and that would return actul/budget data for that specific month + cummulated data from January to that month (financial year = calendar year in our case)


----------



## anteus (Jun 30, 2017)

Sounds like a solution! You mean separate measure for every month possible? Can you give me an example?


----------



## anteus (Jun 30, 2017)

akice said:


> I have done the same thing - SOO and Balance sheet in Excel/Power Pivot using cubes.  What i did was have a disconnect "parameter" table with years and months.  Make your selections with the slicers and then use the values in your measures to drive what period to do the YTD formulas through. Works just fine.



Sorry I meant to reply: do you mean a separate measure for every month possible? Can you give an example?


----------



## gazpage (Jun 30, 2017)

anteus said:


> Sorry I meant to reply: do you mean a separate measure for every month possible? Can you give an example?



I doubt that is what akice means. That would take ages and you would need to create new measures as you got more data.


Create a new table in your data model with all the month / years you need, so Jan 10, Feb 10, Feb 11 etc as one column and the month end date as the other column (31 Jan 2010 etc). Do NOT create any relationships with this table.

Then create a measure like:


```
[YTD measure]:=
CALCULATE (
    SUM ( BaseTable[Values] ),
    FILTER(
        ALL( Dates ),
        Dates[Year] = Year( VALUES ( SlicerTable[Dates] ) ) &&
            Dates[FullDate] <= MAX( SlicerTable[Dates] )
    )
)
```

So when you select say Feb 10 in your disconnected slicer, the only item in the that table in the [Dates] column is 28 Feb 10. As the slicer is disconnected this does not propagate to your other tables.

The CALCULATE() function removes all filters on your Date table (I'm assuming you have one, otherwise include any dates columns in your table in the ALL) and replaces them with filters where the date is earlier than the one you have selected and has the same year. It would be usual to include some HASONEVALUE() functions to protect against multiple selections.

You will have to be a touch more clever if you don't have a 31 Dec year end. Usually dealt with by having a date table


----------



## akice (Jun 30, 2017)

gazpage is absolutely right.  Writing separate measures for every month is contrary to the Dax way.  I have a demo file with test data that demonstrates what i did.  You are welcome to a copy if you want it.  Let me know.


----------



## anteus (Jul 1, 2017)

akice said:


> gazpage is absolutely right.  Writing separate measures for every month is contrary to the Dax way.  I have a demo file with test data that demonstrates what i did.  You are welcome to a copy if you want it.  Let me know.



Hi Akice - I'd love to see it. I am building a demo following gazpage's advice but probably could learn a lot from your file. Thanks in advance!


----------



## anteus (Jul 1, 2017)

gazpage said:


> I doubt that is what akice means. That would take ages and you would need to create new measures as you got more data.
> 
> 
> Create a new table in your data model with all the month / years you need, so Jan 10, Feb 10, Feb 11 etc as one column and the month end date as the other column (31 Jan 2010 etc). Do NOT create any relationships with this table.
> ...


Thanks a lot!


----------



## anteus (Jun 29, 2017)

Hello Power BI forum!
I am trying to develop a PowerPivot based Income Statement report (my previous version is a Power Query table with SUMIFS but that has become extremely slow with 20k records of data - this is mistery to me too) and I cannot figure out how to filter YTD periods with slicer. Let me show the desired structure below:

                        Month Actuals / Month Budget / Month LastYear / YTD Actuals / YTD Budget / YTD LastYear
Revenues
Direct costs
...
...
...

Since the structure is fixed I would go with Cube formulas on the data model but don't know how to deal with YTD with the aim to be able to select any period in the past. The data records contain period, amount, account mapping info, etc.
Any help, hint, idea is appreciated.
Best regards, Laszlo


----------



## akice (Jul 2, 2017)

Here is a link.  Let me know if it doesn't work.

Sample Financial Statement


----------



## anteus (Jul 3, 2017)

akice said:


> Here is a link.  Let me know if it doesn't work.
> 
> Sample Financial Statement



Thanks, it looks great in Excel online. I don't see however the mechanics behind the curtain. While fully understand ip protection concerns could you share some info on the measures used?
Thanks and best regards


----------



## akice (Jul 3, 2017)

anteus said:


> Thanks, it looks great in Excel online. I don't see however the mechanics behind the curtain. While fully understand ip protection concerns could you share some info on the measures used?
> Thanks and best regards



Sorry about that.  Had intended for you to be able to download a copy.  Try this one:

Sample Financial Again


----------



## anteus (Jul 3, 2017)

akice said:


> Sorry about that.  Had intended for you to be able to download a copy.  Try this one:
> 
> Sample Financial Again



Wow, this is great thank you!


----------

