Hi Carlos,

You need to write a DAX measure using the time intelligence function PARALLELPERIOD. It doesn't look like you did the Power Pivot course to learn the DAX functions. Did you already know how to use Power Pivot?

What have you tried so far, do you have a DAX Formula you've tried?

Also, it's difficult to help without knowing your model, so it would help if you can share the model, or at least screenshots.

Mynda

I send one example file of what I need (it is a zip file)

If you do not receive, you can try my onedrive sharing folder, the link is:

https://1drv.ms/f/s!AnON5XxufzhJju9plyZTpeAjc_YOBA

Many thanks for your support

Hi Carlos,

Thanks for sharing the files.

In the attached file you'll see I've added a measure called Prior Year Measure, which uses the PARALLELPERIOD DAX function to calculate the values at a month level for the period 12 months prior. I also inserted a table and line chart that shows the measure is calculating correctly.

Your custom visual looks a little odd because the measure is calculating the total for a month at a time, but the date axis is at day level, so the prior year values appear like a stepped chart. It might be better to use the built in visuals for this data.

To use the time intelligence DAX functions you need a set of consecutive dates in your model. Your KPI Values don't have a date for every day, so I added a Dates table and used that for the Date fields in the measure and visuals.

It looks like you need to learn the Power Pivot side of Power BI so you can write custom measures and understand the modelling requirements. Let me know if you'd like to add the Power Pivot course to your bundle.

Kind regards,

Mynda

Hi Mynda

Almost there

I need to present anytime 12 months from the period I selected, for instance, if I select October 2016, I need to show from September 2015 to October 2016, other example, if I select  quarter 2 of 2016, I need to show July 2015 to June 2016, and always in an continuous line

But, you are wright, I need to learn the Power Pivot or also power query. I will do that for next year

Hi Carlos,

You should use the regular Slicer (not the custom one) and use the 'Between' dates option if you want to choose flexible dates that also update the PARALLELPERIOD measure.

See session 4.06 of the Power BI course for the Slicer options. You might also find the relative slicer useful.

Mynda

Yes, I know, but this is not what I want

The user must only select a period for evaluation, not using a slide with the options between dates.

That's one of the problem, and the rest should be automatically, presenting a full 12 mounts, and the selected date act only as a reference to do that

Maybe not so easy, I hope you can give me a clue

I don't think it can be done. The chart requires a date field in the Axis. You need a DAX measure that returns a table that lists the dates prior to a period you select. You can't use a DAX measure in the Date axis for the chart because the DAX function needs context, AFAIK.

You'll have to educate your users to use the Slicer. Have you tried the relative Slicer where you can choose the 'Last' 1 'Year', or the 'Last' 6 'Months' etc. When you use the 'Prior Year Measure' with the 'Actual' and 'Budget' you get the results you describe.

Mynda

Hi Mynda

Thanks for your support

I already get help on another site, and seems that solution provide to me, is like I need

It's necessary yo have an table with dates and then a new measure using DAX, but with a more convenient approach.

But, thank you very much for your involvement with this issue

Hi Carlos,

Yes, I advised you of the need for a dates table at the beginning.

I'm not sure what you mean by 'I already get help on another site'. Did you find a different solution, if so please share it here.

In future, if you post your question on multiple forums you should declare that you've done so, both here and the other places you post. Forum helpers get very annoyed about duplicating effort.

Mynda

This is what I get:

In the solution below, you need an independent date table.  (No relationship with other tables.).

Indicator =
CALENDAR ( DATE ( 2014, 1, 1 ), DATE ( 2017, 12, 31 ) )

Then create a measure like this:

MeasureActual =
IF (
    MIN ( 'KPI Values'[Date] )
        >= FIRSTDATE (
            DATESINPERIOD ( 'Indicator'[Date], MAX ( 'Indicator'[Date] ), -1, YEAR )
        )
        && MIN ( 'KPI Values'[Date] ) <= MAX ( 'Indicator'[Date] ),
    SUM ( 'KPI Values'[Actual] ),
    BLANK ()
)

Thanks for sharing, Carlos.