Skip to main content
cancel
Showing results for 
Search instead 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

Reply
icdns
Post Patron
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
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi, 

 

@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?

Hi 

 

 

ibarrau
Super User
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!

LaDataWeb Blog

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! 🙂

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

amitchandak
Super User
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.

Hi, 

 

To answer your questions: 

 

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? 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.