Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cumulative area chart based on month name

Hi everyone,

 

I want to create a cumulative area chart based on month name instead of month number. Currently this is my working measure but how can I make it work using month name instead? It won't look good if I use month number as my area chart's x-axis..

 

Cumulative = CALCULATE(SUM(Table1[Value]), FILTER(ALL(Table1[Month Num]), Table1[Month Num] <= MAX(Table1[Month Num])))
 
Hope anyone can help! 
 
Thanks,
BK
Show and Tell
Tips and Tricks
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous you can also add month name column in ALL function

     

    Cumulative = 
CALCULATE(
SUM(Table1[Value]), 
FILTER(
ALL(
Table1[Month Num], 
Table[Month Name]
), 
Table1[Month Num] <= MAX(Table1[Month Num])
)
)

     

    Would appreciate Kudos 🙂 if my solution helped. Ask anything Power BI. Book appointment for a free consultancy at https://www.perytus.com

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous you can also add month name column in ALL function

     

    Cumulative = 
CALCULATE(
SUM(Table1[Value]), 
FILTER(
ALL(
Table1[Month Num], 
Table[Month Name]
), 
Table1[Month Num] <= MAX(Table1[Month Num])
)
)

     

    Would appreciate Kudos 🙂 if my solution helped. Ask anything Power BI. Book appointment for a free consultancy at https://www.perytus.com

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    Try this approach

    1. Create a Calendar Table and write calculated column formulas to extract Year, Month Name and Month number: Year = YEAR(Calendar[Date]), Month Name = FORMAT(Calendar[Date],"mmmm") and Month number = MONTH(Calendar[Date]).  Sort the Month name by the Month number
    2. Create a relationship from the Date column of Data Table to the Date column of the Calendar Table
    3. To your visual, drag the Year and Month from the Calednar Table
    4. Write these measures

    Values = SUM(Table1[Value])

    Cumulative = CALCULATE([Values],DATESYTD(Calendar[Date]))

    Hope this helps.