PowerPivot Income Statement MTD & YTD

[anteus]

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]

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

 

[akice]

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]

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]

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

 

[anteus]

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]

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]

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]

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]

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

Thanks a lot!