Counts Based On Tran Sum Totals As Of A Date

[Coder]

Desired Visual Outcome:

WEEK	TYPE 1	TYPE 2	TYPE 3	TOTAL
3/1/20	0	1	0	1
3/8/20	1	1	0	2
3/15/20	1	1	1	3
3/22/20	1	1	0	2
3/29/20	1	1	0	2

*Edit: Fixing so matrix shows desired logic.

I need to create a matrix where the WEEK is in the Rows section, TYPE is in the Columns section, and a measure needs to be in the Values section. The measure counts ACCTs once per row if the total of TRAN up to that date is above 0. Example: 1 in cell intersecting TYPE 1 and 3/15/2020 means by this date there is one ACCT that has sum of TRAN > 0 measured from the beginning of the TRANS table (see below).

I have a DATES table that has consecutive calendar days in DATE field and a corresponding end of week WEEK date (Shown truncated below).

DATE	WEEK
3/1/20	3/1/20
3/2/20	3/8/20
3/3/20	3/8/20

I have a TRANS table:

INDEX	ACCT	TD	TYPE	TRAN
1	1	3/1/20	2	100
2	1	3/2/20	2	-100
3	1	3/5/20	2	200
4	2	3/4/20	1	150
5	2	3/9/20	1	0
6	3	3/15/20	3	500
7	3	3/16/20	3	-250
8	3	3/16/20	3	-250
9	3	3/23/20	3	100
10	3	3/26/20	3	-100

Note: INDEX counts each row in table. TRANS is ordered by ACCT and TD. Each ACCT can be only one TYPE. There can be multiple transactions on same day, but ACCT should show up only once per WEEK (row on matrix). ACCT should show up on matrix even there are no transactions for the corresponding WEEK date if the total TRAN up to that point for the ACCT is >0.

There is a relationship between tables: 'TRANS'[TD] *:1 'DATES'[DATE]

Matrix should also show only the latest 5 WEEK dates (but getting measure is priority).

Please help me formulate the DAX to create a measure that can go in the Values section of the matrix visual.

[amitchandak]

@Coder , What I got till now. We can use distinctcount of Type

Or create a new concatenated field

Acc+ Type = [Acct]& "-"&[Type]

And take a distinctcount of that. For latest 5 weeks you can use relative date slicer

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

[parry2k]

@Coder I will change measure in @Ashish_Mathur  solution as below so that the Total line at the bottom adds up.

Measure = 
COUNTX (
    SUMMARIZE (
        Trans, 
        Trans[ACCT],
        'Calendar'[WEEK]
    ),
    IF ( [MTD values] > 0, 1)
) + 0

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Coder]

@Ashish_MathurThanks. This is close to what I want.

The counts need to carry over the month and year. So for example if there was an extra transaction for ACCT 3 occurring on 3/29/2020 for 100. The matrix as of 4/12/2020 would show:

WEEK	TYPE 1	TYPE 2	TYPE 3	TOTAL
3/1/20	0	1	0	1
3/8/20	1	1	0	2
3/15/20	1	0	1	2
3/22/20	0	0	0	0
3/29/20	0	0	1	1
4/5/20	0	0	1	1
4/12/20	0	0	1	1

*Red for the rows that wouldn't show up if the matrix only shows last 5 WEEK dates.

Similarly if the matrix was shown around January, the counts would persist from the prior year. Right now it does this only through the end of month.

[Ashish_Mathur]

Hi,

See if this works.  Download the file from here.

[Coder]

@Ashish_MathurThanks for the response. The counts are carrying over from the previous month. However, this is only happening when there is a transaction during the week. For example, if this was the Trans table in the .pbix file (assuming the Calendar table goes to 4/4/2020 in the DATE field):

INDEX	ACCT	TD	TYPE	TRAN
1	1	3/1/20	2	100
2	1	3/2/20	2	-100
3	1	3/5/20	2	200
4	2	3/4/20	1	150
5	2	3/9/20	1	0
6	3	3/15/20	3	500
7	3	3/16/20	3	-250
8	3	3/16/20	3	-250
9	3	3/22/20	3	100
10	3	4/4/20	3	0

The matrix in the file shows:

WEEK	TYPE 1	TYPE 2	TYPE 3	TOTAL
3/1/20	0	1	0	1
3/8/20	1	1	0	2
3/15/20	1	0	1	2
3/22/20	0	0	1	1
3/29/20	0	0	0	0
4/5/20	0	0	1	1

When it needs to be:

WEEK	TYPE 1	TYPE 2	TYPE 3	TOTAL
3/1/20	0	1	0	1
3/8/20	1	1	0	2
3/15/20	1	0	1	2
3/22/20	0	0	1	1
3/29/20	0	0	1	1
4/5/20	0	0	1	1

The matrix needs to show a 1 for TYPE 3 on 3/29/2020 since ACCT 3 has a positive TRAN total at that point, even though it doesn't have a transaction during that week.

[Ashish_Mathur · Accepted solution]

Hi,

If that be your logic, then the result should be this.  Download the PBI file from here.

[Coder]

@Ashish_MathurYes, that's what I'm looking for! You are right my question had an error in it. Sorry about any confusion this may have caused. I will edit the question so your solution may benefit others who want to do something similar.

Thank you!

[Ashish_Mathur]

You are welcome.