Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
goncas
Regular 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

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.

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

 

 

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.

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

Thank you very much

This is what I need for this task

You are great

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.