Summarised trips showing weekday numbers and calculate end date

[Pandadev]

I have a table of trips I am trying to summarise into this format , based on weekday [1 mon] and [7 sunday]

so for the id 01 and code 675 , there are no trips wed and friday , week days 3 and 5 , so a zero is placed in the respective columns , the rest are added as 1 , as there are trips on thos weekdays , the effective data will be the earliest date and the end date would be the max date , if the max date date is less than the max date of all the data , so in the case of code 675 the last trip took place on 26/10/21 and the max date was 31/10/21 so it could of had trips on 30/10/21  and  31/10/21

for 676 n/a is added as the last day of 31/10/21 was a date that there was no trip for 676 , so it was unknown

id   code    effective date  end date   Mon  Tue  Wed  Thu  Fri  Sat  Sun

01   675      01/10/21         26.10.21       1      1       0       1     0     1    1

01  676       01/10/21         n/a               1      1       1       1     1     0    0

raw data feed

id  code     date          weekday

01  675      02/10/21      6

01  675      03/10/21      7

01  675      04/10/21      1

01  675      05/10/21      2

01  675      07/10/21      4

01  675      09/10/21      6

01  675      10/10/21      7

01  675      11/10/21      1

01  675      12/10/21      2

01  675      14/10/21      4

01  675      16/10/21      6

01  675      17/10/21      7

01  675      18/10/21      1

01  675      19/10/21      2

01  675      21/10/21      4

01  675      23/10/21      6

01  675      24/10/21      7

01  675      25/10/21      1

01  675      26/10/21      2

01  675      28/10/21      4

01  676      04/10/21      1

01  676      05/10/21      2

01  676      06/10/21      3

01  676      07/10/21      4

01  676      08/10/21      5

01  676      11/10/21      1

01  676      12/10/21      2

01  676      13/10/21      3

01  676      14/10/21      4

01  676      15/10/21      5

01  676      18/10/21      1

01  676      19/10/21      2

01  676      20/10/21      3

01  676      21/10/21      4

01  676      22/10/21      5

01  676      25/10/21      1

01  676      26/10/21      2

01  676      27/10/21      3

01  676      28/10/21      4

01  676      29/10/21      5

[Pandadev]

on the end date I was trying to see if it was the max date based on if the weekday number exist , so if the end date was earler than the max date where a 1 exist in the weekday column , then add the end date , so for today the max date would be 27th Sep 2021 , if the end date was showing Tue 21st Sept , and it operated on Tue , Thu , Fri , Sat , it should have had an end date of 25th Sep 2021 as the end date . so I would need to know that 21st Sept was the end date

[Pandadev]

Is it possible to create columns , instead of using a matrix , as I need to comapare two data sets , and the other data set has columns named  1,2,3,4,5,6,7 , with either a 1 or 0 added

[v-shex-msft]

HI @Pandadev,

Nope, you can't convert this to calculate column format. Some of the records did not really include in your table so the Dax formula does not calculate these missed records. You need to manually add these missed records then they can show a similar effect on the matrix. (parameter table field is used to expand records)

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @Pandadev,

I'm still not so clear about the 'end date' field calculation logic, can you please explain more about these?

How to Get Your Question Answered Quickly

For other parts, please check the following sample file if helps:

Regards,

Xiaoxin Sheng

[Pandadev]

Thanks that is nearley perfect , the only thing i need different is that all the values that are not zero to be a 1 , instead of the count values , so in your examples all the values taht are showing as 4 to show as 1.

[v-shex-msft · Accepted solution]

Hi @Pandadev,

You can modify the 'count date' formula to use 'distinctcount' function with the 'code' filed to calculate the distinct count of records.

count date =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[code] ) + 0,
    FILTER ( ALLSELECTED ( 'Table' ), [weekday] IN VALUES ( 'ParaTable'[Value] ) ),
    VALUES ( 'Table'[code] ),
    VALUES ( 'Table'[id] )
)

Regards,

Xiaoxin Sheng