Plot line chart of current month by day vs average of last N months by day

[Anonymous]

Trying to plot a chart like show above with running count of participation. Line chart showing participation at days before and after due date with a comparison to rolling average of same days of last 3 months.

Fields in the data model,

• DueDate
• ParticipationDate
• ReportingMonth
• UserID

I am trying to replicate following table with DAX where ParticipationDate is +-5 from DueDate.

Month	-5d	-4d	-3d	-2d	-1d	Due	+1d	+2d	+3d	+4d	+5d
Jan'20	20%	22%	25%	28%	35%	50%	80%	90%	95%	95%	96%
Feb'20	0%	20%	30%	45%	50%	70%	80%	95%	96%	96%	96%
Mar'20	20%	22%	25%	28%	35%	70%	80%	90%	92%	94%	96%
Avearge	13%	21%	27%	34%	40%	63%	80%	92%	94%	95%	96%
Apr'20	20%	30%	40%	50%	65%	85%

[v-jayw-msft]

Hi @Anonymous ,

Is the last table a fact table in your model or the result you want to get?

Best Regards,

Jay

[Anonymous]

It is the result I am trying to get.

Here is the DAX I have gotten so far, for cumulative participation during current month. However, it does not work well if previous months are selected.

Participation % =
DIVIDE (
    CALCULATE (
        COUNTROWS ( MyTable ),
        FILTER (
            ALLSELECTED ( MyTable ),
            MyTable[ParticipationDate] <= LASTDATE ( MyTable[ParticipationDate] )
        ),
        MyTable[TimeReportStatus] = "Complete",
        ALLSELECTED ( MyTable[ReportName] ),
        DATESINPERIOD (
            'MyTable'[ParticipationDate],
            LASTDATE ( MyTable[ParticipationDate] ),
            -30,
            DAY
        )
    ),
    CALCULATE (
        COUNTA ( 'MyTable'[Login] ),
        ALLSELECTED ( 'MyTable'[ReportMonth], MyTable[ParticipationDate] )
    )

Sample Data:

ReportMonth	ReportStartDate	ReportEndDate	ReportDueDate	Login	ParticipationDate
Apr-20	4/1/2020	4/30/2020	5/1/2020	Arya	4/30/2020
Mar-20	3/1/2020	3/31/2020	4/1/2020	John	3/30/2020
Mar-20	3/1/2020	3/31/2020	4/1/2020	Theon	3/30/2020
Apr-20	4/1/2020	4/30/2020	5/1/2020	Bran	4/27/2020
Apr-20	4/1/2020	4/30/2020	5/1/2020	Tywin	4/28/2020
Mar-20	3/1/2020	3/31/2020	4/1/2020	Arya	3/31/2020
Mar-20	3/1/2020	3/31/2020	4/1/2020	Bran	3/24/2020
Mar-20	3/1/2020	3/31/2020	4/1/2020	Sansa	3/26/2020
Mar-20	3/1/2020	3/31/2020	4/1/2020	Jaime	4/2/2020
Mar-20	3/1/2020	3/31/2020	4/1/2020	Tyrion	3/31/2020