cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
goncas Frequent Visitor
Frequent Visitor

Filter of some period, and get the information from the previous 12 months

I have Financial data from 4 years, and I want to get the information based on Time filters, for instance November 2016, and I need to get a line graph with this date, but from December 2015 to November 2016. It means allways 12 month's before the date I choose.

I'm completly bloked, can someone give me an help on this?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter of some period, and get the information from the previous 12 months

Hi @goncas,

 

In the solution below, you need an independent date table.  (No relationship with other tables.). You can try it in this file.

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 ()
)

Filter_of_some_period_and_get_the_information_from_the_previous_12_months

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Super User
Super User

Re: Filter of some period, and get the information from the previous 12 months

When you said "Date I choose", what does that mean? Are you saying when you select a date in a slicer/filter on the report or what?




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

www.perytus.com
(MVP Data Platform)


goncas Frequent Visitor
Frequent Visitor

Re: Filter of some period, and get the information from the previous 12 months

Hi, Thanks for your fast repply

 

I will try to explain better

 

I have a finacial table with several data, Year, Month, (I also convert this two to date collumm), Actual Value, Budget Value, Previous Year Value, Country and so on

 

I use a Hierarchie Filter, using the Date collumm, to filter the date I want. (for instance: November 2016)

 

After selected this date, I need to show a line chart containing values from December 2015 to November 2016.

 

If necessary I can send an example of this

Super User
Super User

Re: Filter of some period, and get the information from the previous 12 months

If you are open to share sample data thru google drive or other method, it will help. 




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

www.perytus.com
(MVP Data Platform)


goncas Frequent Visitor
Frequent Visitor

Re: Filter of some period, and get the information from the previous 12 months

Thanks

 

I create an example file

 

One pbix and one xlsx

 

I use Onedrive, and the link are:

 

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

 

 

Community Support Team
Community Support Team

Re: Filter of some period, and get the information from the previous 12 months

Hi @goncas,

 

In the solution below, you need an independent date table.  (No relationship with other tables.). You can try it in this file.

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 ()
)

Filter_of_some_period_and_get_the_information_from_the_previous_12_months

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
goncas Frequent Visitor
Frequent Visitor

Re: Filter of some period, and get the information from the previous 12 months

Thank you very much

This is what I need for this task

You are great

Highlighted
lumi Frequent Visitor
Frequent Visitor

Re: Filter of some period, and get the information from the previous 12 months

Hi @v-jiascu-msft,

Thank you for your example.

Could you please let me know why did you use MIN? 

 MIN ( 'KPI Values'[Date] )

 
It seems like the IF statment will always be false - or I am missing something.

Thanks,

Lumi