Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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 () )
Best Regards,
Dale
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 () )
Best Regards,
Dale
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |