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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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?


@ 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!:
Mastering Power BI 2nd Edition

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.