Showing SUM of values in interval in Matrix

[Pavlous]

Hey!

I have ran into a problem with one of my report.

The demand is to set  "Total to process" things which came from data source (unchangable). I have 3 type of dates here - Received Date, Due Date and End Date. They are connected to calendar table in PBI to make a list of days in a Matrix. The thing is that they are connected by End Date -> Date so values are shown by this preset. If I want to show summary of values coming from lines it will shown only "to process" lines by the end date. I want to show all unprocessed values until the date, including the date. It means that the line was active from for example 1/1/2017 until 5/1/2017 with 5/1/2017 end date. So the sum value takes this line only for 5/1/2017 and I need it to be shown since 1/1/2017 until 5/1/2017 included.

I was triing to play with function datesbetween in calculation function with not correct resolutions.

Any ideas?

[v-shex-msft · Accepted solution]

Hi @Pavlous,

You can try to use below formula to create the output table:

Summary Table = 
var temp= SELECTCOLUMNS(DISTINCT(UNION(VALUES('Sample'[StartDate]),VALUES('Sample'[EndDate]))),"Date",[StartDate])
return
ADDCOLUMNS(temp,"Total", SUMX(FILTER(ALL('Sample'),'Sample'[StartDate]=[Date]||'Sample'[EndDate]=[Date]),[Count]))

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

Hi @Pavlous,

You can try to use below formula to create the output table:

Summary Table = 
var temp= SELECTCOLUMNS(DISTINCT(UNION(VALUES('Sample'[StartDate]),VALUES('Sample'[EndDate]))),"Date",[StartDate])
return
ADDCOLUMNS(temp,"Total", SUMX(FILTER(ALL('Sample'),'Sample'[StartDate]=[Date]||'Sample'[EndDate]=[Date]),[Count]))

Regards,

Xiaoxin Sheng

[Pavlous]

Nice! @v-shex-msft

Thank you, it is usefull, but can you help me a bit more? How to agregate this code to multi-date difference? If there are more results with differences from start date to end date ( 1, 2, 3, 4...) days?

[v-shex-msft]

Hi @Pavlous,

Can you please share some sample data?

Regards,

Xiaoxin sheng

[Pavlous]

Hi @v-shex-msft

Sure I can, and also lets say that I am interested in only category "A"

09/08/2017	11/08/2017	C	3
09/08/2017	11/08/2017	C	1
09/08/2017	11/08/2017	C	1
09/08/2017	11/08/2017	C	1
09/08/2017	11/08/2017	C	1
09/08/2017	11/08/2017	C	1
09/08/2017	13/08/2017	C	1
09/08/2017	13/08/2017	C	1
09/08/2017	13/08/2017	C	1
09/08/2017	09/08/2017	A	21
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	11
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	3
09/08/2017	10/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	12
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	2
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	15/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	12/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	12/08/2017	A	25
09/08/2017	14/08/2017	B	4

[v-shex-msft · Accepted solution]

H @Pavlous,

Please check below formula if it suitable for your requirement:

Result=
VAR calendar_date =
    CALENDAR (
        CALCULATE ( FIRSTDATE ( Test[ReceivedDate] ), Test[Categories] = "A" ),
        CALCULATE ( LASTDATE ( Test[EndTime] ), Test[Categories] = "A" )
    )
VAR filted =
    FILTER ( ALL ( Test ), [Categories] = "A" )
RETURN
    ADDCOLUMNS (
        calendar_date,
        "Total", SUMX (
            FILTER (
                ADDCOLUMNS (
                    filted,
                    "Exist", CONTAINS (
                        SELECTCOLUMNS ( CALENDAR ( [ReceivedDate], [EndTime] ), "CheckDate", [Date] ),
                        [CheckDate], [Date]
                    )
                ),
                [Exist] = TRUE ()
            ),
            [To process]
        )
    )

Regards,

Xiaoxin Sheng

[Pavlous]

@v-shex-msft

Nice!

Thank you man! Working just perfectly!

I have to admit, that I am not even close with my knowledge in comparison with you.

[Pavlous]

Hi @v-shex-msft

Sure... there they are. I am also interested only in Categories "A"

Received Date	End Time	Categories	To process
09/08/2017	11/08/2017	C	3
09/08/2017	11/08/2017	C	1
09/08/2017	11/08/2017	C	1
09/08/2017	11/08/2017	C	1
09/08/2017	11/08/2017	C	1
09/08/2017	11/08/2017	C	1
09/08/2017	13/08/2017	C	1
09/08/2017	13/08/2017	C	1
09/08/2017	13/08/2017	C	1
09/08/2017	09/08/2017	A	21
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	11
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	3
09/08/2017	10/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	25
09/08/2017	11/08/2017	A	12
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	2
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	15/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	12/08/2017	A	25
09/08/2017	10/08/2017	A	25
09/08/2017	12/08/2017	A	25
09/08/2017	14/08/2017	B	4