Forum Discussion

Kostas's avatar
Kostas
Helper IV
4 years ago

Line Percentage MoM completion Comparison

Hello Everyone, 

Once again need your help. 
I am having the below table

ReferenceStatusDate CompletionPlan Year
Ref1Complete05/06/20202020
Ref2Incomplete 2020
Ref3Complete08/06/20212021
Ref4Complete09/11/20212021
Ref5Complete08/04/20222022
Ref6Complete02/08/20222022

 

What I need is to produce that shows the percentage completion on month by month basis. Each line in the line graph to present the percentage completion of each year as to the total number of references for each plan year. Something Like:

Where each line will be in legend:

Blue = 2022 (In progress)

Red = 2021

Purple  = 2020

and in the X axis I will have the months (i.e. January February March etc.)

 

What I have done already was to create a calendar table and create the following measure one for each year (2020,2021,2022):

 

 

Completed Proportion 2020 = 
Var Measurement = 
IF(
    MAX('Calendar'[Date])  <= TODAY() || 
    MAX('Calendar'[Date].[Year]) = YEAR(TODAY()) && MAX('Calendar'[Date].[MonthNo]) = MONTH(TODAY()) ,
	 CALCULATE (
        DISTINCTCOUNT ('Table'[Reference] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date Completion] <= MAX ( 'Calendar'[Date]) 
        ) ,
         NOT(ISBLANK('Table'[Date Completion] )), 'Table'[Status] = "Complete",
         'Table'[Plan Year] = "2020"
    )
,
BLANK()
)

Var TotalAudits = 
CALCULATE(DISTINCTCOUNT('Table'[Reference]), 'Table'[Status] <> "Incomplete",'Table'[Plan Year] = "2020")

RETURN
Measurement/TotalAudits

 

 

But when I placing all three measures in my line graph I am not getting it in MoM basis but as total delivery throughout the three years. It works only when I drill down to split the hierarchy while when I am choosing to just drill down to next level it does not return any results.


Can you please assist?

2 Replies