Display the count by month in a graph

[c1c]

Hi all

I want to display the count by month in a graph

Currently, I have this data:

I want to display like this:

x axis : month

y axis : count by limit date

            count by Acceptance date

I know the methods to use measure

I would like to know other one.

Any ideas on how to do it?

Thank you so much.

[Anonymous · Accepted solution]

Hi @c1c 

Based on your description, you can refer to thw follwing solutions.

Create a calendar table.(there is no relationship between tables)

Calendar = CALENDAR(DATE(2023,1,1),DATE(2024,12,31))

Solution 1.Use measure:

Create the following measures.

Count_acceptdate =
CALCULATE (
    COUNTA ( 'Table'[Job] ),
    EOMONTH ( 'Table'[Acceptance Date], 0 )
        = EOMONTH ( MAX ( 'Calendar'[Date] ), 0 )
)

Count_limitdate =
CALCULATE (
    COUNTA ( 'Table'[Job] ),
    EOMONTH ( 'Table'[limit Date], 0 ) = EOMONTH ( MAX ( 'Calendar'[Date] ), 0 )
)

Then put the date column of the calendar table to x-axis, put the measures to y-axis

Output

Solution 2. In power query, unpivot your table to the following format.

Then create a relationship between date table and the data table

Put the date column of the calendar table to x-axis, then put the job field of the data table to y-axis, and set it as 'count of job', and put the attribute value to the legend.

Output

Best Regards!

Yolo Zhu

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