cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## SHOW LATEST 3 MONTH IN BAR GRAPH

Hi guys,

Could somebody help me in displaying the LATEST 3 MONTHS data that will show in my bar graph. I have my DIM_CALENDAR and Date_Key as my dimension which is connected to my Date column in my FCT table

Example scenario:

My 2020 data has January, February, March and April.

If I filter 2020 in my YEAR filter, it will show the latest 3 months (Feb, March & April)

Note: It is only dependent on my YEAR filter

Ex.

Filter: 2020

Jan - 95%

Feb - 98%

Mar - 92%

April - 91%

Result Visual - (Display in Bar) :

Feb - 98%

Mar - 92%

April - 91%

I have used this formula:

LATEST_3_MONTHS = CALCULATE(sum(FCT_TABLE[ PERCENTAGE ]),DATESINPERIOD('DIM_CALENDAR'[FullDateAlternateKey],MAX(FCT_TABLE[DATE]),-3,MONTH))

But it is not working 😞

Thank you!

9 REPLIES 9
Super User

@icdns - Can you just use a relative date filter? Last 3 months?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Post Patron

Hi,

Super User

@icdns , and year filter is from the date Table?  I am assuming the second reply does not work.

Can you share sample data and sample output in table format?

Post Patron

Hi

Super User

Hi. When I want to show last 3 months of the selected period I usually use this:

``````Measure L3M =
VAR _t = MAX('Date'[Date])
VAR _End = EOMONTH( _t , 0 )
VAR _Start = EOMONTH( _t , -3 ) +1
RETURN
CALCULATE (
[Measure],
DATESBETWEEN( 'Date'[Date] , _Start , _End )
)``````

Hope this helps,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

Post Patron

Hi,

I'm sorry for the confusion but it is NOT affected by my month filter. Only with the YEAR filter. So whatever my year is, it will only show the latest 3 months of data for that year.

Thanks! 🙂

Super User

@icdns , Try like

LATEST_3_MONTHS =
var _max = calculate(MAX(FCT_TABLE[DATE]), filter(all(Date), Date[year] =selectedvalue(Date[Year])))
return
CALCULATE(sum(FCT_TABLE[ PERCENTAGE ]),DATESINPERIOD('DIM_CALENDAR'[FullDateAlternateKey],_max,-3,MONTH))

Super User

@icdns , This formula seems correct to me

Hope FullDateAlternateKey is date column ?

DIM_CALENDAR is marked as date table ?

DIM_CALENDAR has all the required Dates?

LATEST_3_MONTHS = CALCULATE(sum(FCT_TABLE[ PERCENTAGE ]),DATESINPERIOD('DIM_CALENDAR'[FullDateAlternateKey],MAX(FCT_TABLE[DATE]),-3,MONTH))

Try to select a month using slicer and check if it works.

if it does not work

Can you share a sample pbix after removing sensitive data.

Post Patron

Hi,

Hope FullDateAlternateKey is date column ? yes

DIM_CALENDAR is marked as date table ? yes

DIM_CALENDAR has all the required Dates? yes

I just thinked that it only depends on my YEAR filter, not month filter.

Whatever my year is. it will only show the latest 3 months of data.

Can I still use this formula?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors