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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
unnijoy
Post Partisan
Post Partisan

Latest month in X Axis

MONTHREIGIONSALES
DEC-1-2020INDIA100
DEC-1-2020APAC245
DEC-1-2020ASIA345
DEC-1-2020NA56
DEC-1-2020PHIL63
DEC-1-2020UAE345
JAN-1-2021INDIA563
JAN-1-2021APAC786
JAN-1-2021ASIA776
JAN-1-2021NA866
JAN-1-2021PHIL875
JAN-1-2021UAE345

 

As per the above data i have the Month filter. 

 

I need to create a graph (clustered bar chart)  which will show the region wise sales.

 

Criteria - If am selecting all the month then it should show only the latest month data. And if am selecting multiple months then only it should show multiple month data.

 

i need this is clustered bar chart.

1 ACCEPTED SOLUTION

Hi @unnijoy ,

You can create a calendar table with the below formula:

Calendar =
VAR _mindate =
    MIN ( 'FactTable'[Date] )
VAR _maxdate =
    MAX ( 'FactTable'[Date] )
RETURN
    CALENDAR ( _mindate, _maxdate )

Then create a calculated column as below in this calendar table and drag it on the slicer:

MonthYear = FORMAT('Calendar'[Date],"MMM-YY")

calendar table.JPG

Best Regards

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

View solution in original post

11 REPLIES 11
MFelix
Super User
Super User

Hi @unnijoy ,

 

Use the following measure:

Calculation per selection =
IF (
    ISFILTERED ( 'Table'[MONTH].[Month] ),
    SUM ( 'Table'[SALES] ),
    CALCULATE (
        SUM ( 'Table'[SALES] ),
        FILTER ( ALL ( 'Table'[MONTH] ), 'Table'[MONTH] = MAX ( 'Table'[MONTH] ) )
    )
)

 

MFelix_0-1611848614325.png

 

MFelix_1-1611848653552.png

Check PBIX File attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi, @MFelix ,

 

Thanks for your reply. I need to add month to the legend in clustered bar graph. But when i put the month as leend and when i lesect all month insted of showing the latest month it is showing all month. Can you help me to fix this .

 

I need the month is legend.

And when all months are selected it should show only the latest month. so their will be only one bar. And if i select multiple month manually tnen it shouls show multiple bars based on the selction.

unnijoy_0-1611898751142.png

in the above screen shote u can see that all months are selected. currently it is showin multiple months. can we make it only to January. And if i select 2 months then only it shoud show 2 month graph.

 

Thannks in Advance. 🙂

 

 

 

Hi @unnijoy ,

 

For this you need to have a calendar table related with your table and two columns on the Sales with Month and Month name then use the following measure and format:

 

Calculation per selection = 
VAR maximum_date_value =
    MAXX ( ALL ( 'Table'[MONTH] ), 'Table'[MONTH] )
RETURN
    IF (
        ISFILTERED ( 'calendar'[Month] ),
        SUM('Table'[SALES]),
        CALCULATE (
            SUM ( 'Table'[SALES] ),
            FILTER (
                ALL ( 'calendar'[Year], 'calendar'[Monthnº] ),
                'calendar'[Year] = YEAR ( maximum_date_value )
                    && 'calendar'[Monthnº] = MONTH ( maximum_date_value )
            )
        )
    )

 

Setup of you visualizations:

  • Slicer - Month from the calendar table
  • Graph:
    • Axis Region
    • Legend Month Name from the sale table
    • Value measure above

MFelix_0-1611930271656.pngMFelix_1-1611930292647.png

PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ,

 

This work perfect. Only thin i need help is to change the month in the flter to be in Mmm-YY format. And currently it is showing all month. We need that to be based on the month data avilabe in the data. Because we are having other graph in the same page. So we need to use the same month filter. 

Please help me to get the month filter in MMM-YY format. 

Hi @unnijoy ,

 

Not really sure how you have your setup but if ytou add a column with the following code this should do the trick:

 

Month-Year = Format(Table[Date], "mmm,yy")

 

Also you will need to add a sorting column for the previous one with the following code:

 

Month-Year = Format(Table[Date], "yyyymm")

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ,

 

Can we customise the Calander table in such a way that it will show the month and year based on the data available in the main Table. so the Calander table will show Only Decemner 2020 and January 2021. When we add moer data in 2021 based on that the mnths whouls add on. In your above set up the filter that we give to clinet will have all month. And even if give the Year filter they will get confused.

Also can we make the month column in Calander Table to MMM-YY. Currently it will show only MMM. 

Hi @unnijoy ,

 

Sorry for the late response you need to do the following:

 

  • Do your calendar with the following code:
calendar = 
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[MONTH] ); MAX ( 'Table'[MONTH] ) );
    "Year"; YEAR ( [Date] );
    "Monthnº"; MONTH ( [Date] );
    "Month"; FORMAT ( [Date]; "mmmm" );
    "Month Year";FORMAT([Date];"mmm-yy");
    "MonthYearSort"; FORMAT([Date];"yyyymm")
)

If your calendar table is done in Advance Editor you just need to make sure that the start date and end date match with the minimum date and maximum date of your data.

  • Add the following column to your data table:
MonthYear = FORMAT('Table'[MONTH];"YYYYMM")

 

  • Reajust the measure to the following code:
Calculation per selection = 
VAR maximum_date_value =
    MAXX ( ALL ( 'Table'[MonthYear] ); 'Table'[MonthYear] )
RETURN
    IF (
        ISFILTERED ( 'calendar'[Month Year] );
        SUM('Table'[SALES]);
        CALCULATE (
            SUM ( 'Table'[SALES] );
            FILTER (
                ALL ( 'calendar'[MonthYearSort]);
                'calendar'[MonthYearSort] =  maximum_date_value 
            )
        )
    )

 

Result below and in attach PBIX file.

 

MFelix_0-1612779074026.pngMFelix_1-1612779089745.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @unnijoy ,

 

You want to have the slicer by the Month Year of the calendar table is that it?

 

So you oinly show the values that have actuals? Is this correct?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ,

 

You are correct. Lets say currently i have data from December 2020 - January 2021. So the slicer should show only Dec-20 & Jan 21. Next time when i add February 2021 data slicer should show Dec-20, Jan-21 & Feb-21.

So in short the calander table should update the month based on the month available on the main data table. But now in calender table it is showing all month.

 

How can we make the calander table based on Month data availabe in Main Data Table.

 

Please note we need the month format in MMM-YY.

 

Thanks you verymuch for your patience and help.

Hi @unnijoy ,

You can create a calendar table with the below formula:

Calendar =
VAR _mindate =
    MIN ( 'FactTable'[Date] )
VAR _maxdate =
    MAX ( 'FactTable'[Date] )
RETURN
    CALENDAR ( _mindate, _maxdate )

Then create a calculated column as below in this calendar table and drag it on the slicer:

MonthYear = FORMAT('Calendar'[Date],"MMM-YY")

calendar table.JPG

Best Regards

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

hi @v-yiruan-msft ,

 

Thank for the help. But ho i i make the graph availabe so that if all month is selected then it will show only latest month data. And if multiple months are selected then it should show selected months.

 

I need the clustered Bar chart.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.