Cummulative counter + calendar + show items with no data

[jgo1986]

Hello,

I am stucked and I would like to ask you for help.

I have the following table, with a list of incidents of several years  and several "causes":

My fiscal year starts in week 14 and ends in week 13 of the following year, so after creating a #FY_NUM column wich classifies every incident belonging to FY1415, FY1516 ... FY1920, I created a line&clustered chart like:

where the colum counts the number of incidents in  column [Idenfity] and the line is a cummulative count of the incidents (see there are 2 filters, one for FY and one for Incident Cause):

## Cummulative = CALCULATE(COUNT(Incidencias[identify]);FILTER(ALL(Incidencias[Custom]);Incidencias[Custom]<=MAX(Incidencias[Custom]));VALUES(Incidencias[# FY_NUM]))

Unfortunatelly, I needed to put in axis all FY weeks, so i fixed this creating a calendar and making a relationship between calendar table and Incidents table. The problem is that now I can't represent the cummulative count of incidents:

My columns are being countered well, but I need to draw the cummulative count as the red line I painted in the chart above instead of what I am representing in (## Cummulative_2) line. This is the "Cummulative_2" calculated measure that im representing in blue:

## Cummulative_2 = CALCULATE(COUNTROWS(Incidencias);FILTER(ALLSELECTED(Incidencias);Incidencias[date]<=MAX(Incidencias[date])))

1. Can you help me to change "## Cummulative_2" measure so I can represent a complete cummulative line?
2. Given the correct Cummulative_2 formula, as you can see the chart is filtering data belonging to FY1516. Is there a way to create a Cummulative_3 measure which calculates the FY1415  (the previous year given a filter) to represent both FY (the one filtered and the previous)?

Thanks in advance.

Kind regards,

Jorge

[v-juanli-msft]

Hi @jgo1986 

I can't reproduce your problem.

Please check my pbix to see if my test fit your scenario.

I use this formula for ## Cummulative_2 

clc1 = CALCULATE([count],FILTER(ALLSELECTED(Sheet1),Sheet1[FY]=MAX(Sheet1[FY])&&[date]<=MAX(Sheet1[date])))

As for Cummulative_3 measure which calculates the previous year,

You could consider SAMEPERIODLASTYEAR function.

similar thread:

DAX Last Year Sales

Best Regards
Maggie

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

[jgo1986 · Accepted solution]

Hello Maggie,

First of all thanks for having answered me. 

Following your instructions I changed my measure to:

## Cummulative2 = CALCULATE(COUNT(Incidencias[identify]);FILTER(ALLSELECTED(Incidencias);Incidencias[# FY_NUM]=MAX(Incidencias[# FY_NUM]) && Incidencias[date]<=MAX(Incidencias[date])))

If I use as Shared Axis the Week letter of the "Incidencias" table, I can represent the line, but the problem is that there is not an incident every week so the chart only represents those week where an incident occurred. 

But when I Relate the "Incidencias" Table with a Calendar Table which has all the weeks and I press the option "show items with no data" from the chart Shared Axis, the line disappears 😞

I have seen your "sheet1" table have values for "identify" field for all weeks, could you try to remove some weeks in order to see what happens with your chart line?

PS: As my incident table dates have format "dd/mm/yyyy hh:mm:ss" and my calendar table dates have format "dd/mm/yyyy 00:00:00", I relate both tables by a date_as_int column:

date_as_int=FORMAT ( [Date]; "YYYYMMDD" );

Calendario = 
ADDCOLUMNS (
CALENDAR (DATE(2015;01;01); DATE(2021;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"WeekNumber"; WEEKNUM( [Date];2);
"YearMonthnumber"; FORMAT ( [Date]; "YYMM" );
"YearMonthShort"; FORMAT ( [Date]; "YY/mmm" );
"YearMonthShort2"; FORMAT ( [Date]; "mmm/YY" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"WeekNameShort";IF(WEEKNUM([Date];2)<10; CONCATENATE("W0"; WEEKNUM([Date];2));CONCATENATE("W"; WEEKNUM([Date];2)));
"DayOfWeekNumber"; WEEKDAY ( [Date];2 );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" );
"YearWeek"; CONCATENATE(IF(WEEKNUM([Date];2)<10;CONCATENATE("W0";WEEKNUM([Date];2));CONCATENATE("W";WEEKNUM([Date];2)));CONCATENATE("'";YEAR([Date])));
"FYWEEK_NUM"; IF(WEEKNUM( [Date];2)>=14;WEEKNUM( [Date];2)-13;WEEKNUM( [Date];2)+39)
)

Kind regards,

Jorge.