Hi. I have a matrix with a date hierarchy as rows and count of salesIDs as values.
The datetable and the salestable are related via [Date] and [CREATEDDATE], which are the same date format, but there might be dates missing in the [CREATEDDATE] column, since we don’t always create orders on weekends.
My problem:
The matrix makes a row of “Blank Year” values. And in that row, i get a count of salesIDs for my running totals.
When I look at what salesIDs I get in the blank rows, they are SalesIDs that also exist in other rows, where the dates are not blank.
Why does my matrix duplicate my SalesIDs and put the duplicates in a blank row?
And how can i change my running totals to avoid it?
I am not allowed to share the dataset, but I have screenshots of the matrix and the measure:
Matrix:
Matrix expanded:
Blank part of matrix expanded even further:
Measure [OrdersL12M]:
OrdersL12M =
VAR EndDate2 = MAX(Kalender[Dato])
VAR StartDate2 = EDATE(EndDate2;-12)+1
VAR Result2=
CALCULATE(
[Count of SalesID];
DATESBETWEEN(Kalender[Dato];StartDate2;EndDate2)
)
VAR EndDate1 = TODAY()
VAR StartDate1 = EDATE(EndDate1;-12)+1
VAR Result1=
CALCULATE(
[Count of SalesID];
DATESBETWEEN(Kalender[Dato];StartDate1;EndDate1)
)
RETURN
IF(SELECTEDVALUE(Kalender[Year])=YEAR(TODAY());
Result1;Result2)
Measure [OrdersL12MLY]:
OrdersL12MLY =
VAR EndDate2 = [StartDate-1Y]
VAR StartDate2 = EDATE(EndDate2;-12)+1
VAR Result2=
CALCULATE(
[Count of SalesID];
DATESBETWEEN(Kalender[Dato];StartDate2;EndDate2)
)
VAR _today = TODAY()
VAR _LastYear = YEAR(TODAY())-1
VAR EndDate1 = DATE(_LastYear;MONTH(_today);DAY(_today))
VAR StartDate1 = EDATE(EndDate1;-12)+1
VAR Result1=
CALCULATE(
[Count of SalesID];
DATESBETWEEN(Kalender[Dato];StartDate1;EndDate1)
)
RETURN
IF(SELECTEDVALUE(Kalender[Year])=YEAR(TODAY());
Result1;Result2)
Measure [Startdate-1Y]:
StartDate-1Y =
VAR Slut = LASTDATE(Kalender[Dato])
VAR _Start = DATEADD(Slut;-12;MONTH)
RETURN
_Start