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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Show Graph From First Month until filtered Month of the Year

Hi All,

 

I have request where I need the user to review sales numbers each month from 1st month till selected month.

 

I managed to do this usind below measure

 

 

Sales up to selected month = 

CALCULATE ( 
    [Sum Of Sales],
    FILTER( 
        dim_date, 
        MAX ( dim_date[Date]) <= MAX ( DisconnectedDate[Date] )
    )
)

 

 

This works well with data for one year only, and if I add another year it simply sums the sales numbers for each month. I want the user to select a year from a slicer and then review sales that year per month. 

 

Relations is a below:

 

Relations.JPG

 

Your assistance is much appreciated - thanks!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , You do not want to show a trend YTD can work with date table and time intelligence

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

 

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

 

In case you want show trend you need independent date table in slicer

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max,-1*MONTH(_max))+1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , You do not want to show a trend YTD can work with date table and time intelligence

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

 

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

 

In case you want show trend you need independent date table in slicer

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max,-1*MONTH(_max))+1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hi @amitchandak,

 

Thanks a million for your reply. Which was much helpfull. 

 

I manged to solve the puzzle with below measure as you suggested: 

Sales YTD upto selected month = 

var _max = maxx(ALLSELECTED(DisconnectedDate),DisconnectedDate[Date])
var _min = eomonth(_max,-1*MONTH(_max))+1

return

Calculate([Sum Of Sales], Filter(dim_date,dim_date[Date] <= _max && dim_date[Date] >=_min))

 

Tables are now filtering as requested! Perfect 🙂

 

Measure.JPG

 

Thanks once again!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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