New table based on values calculated by measures (distinct count of values in another table)

[pooling]

I have a table with shipments data:

Calendar	Consignor	Shipment#	Client	ShippingType	Carrier
24/05/2021 00:00	Cons_A	430174	Clie_X	LTL	Carr_1
26/05/2021 00:00	Cons_B	432496	Clie_Y	FTL	Carr_1
26/05/2021 00:00	Cons_C	432499	Clie_Z	FTL	Carr_2
27/05/2021 00:00	Cons_A	432732	Clie_Z	FTL	Carr_3
27/05/2021 00:00	Cons_B	432736	Clie_Y	LTL	Carr_3
28/05/2021 00:00	Cons_C	434673	Clie_X	FTL	Carr_3
28/05/2021 00:00	Cons_A	433475	Clie_Y	FTL	Carr_2
24/06/2021 00:00	Cons_B	456648	Clie_X	LTL	Carr_1
24/06/2021 00:00	Cons_C	454803	Clie_Z	FTL	Carr_1

Calendar column: contains dates for each shipment. We will be interested to see results aggregated by months (I created a separate calendar table with a column YEAR-MONTH).

Consignor column: the key column here. Based on performance of each consignor during the selected month (in slicer), we need to assign a status to it (formula will be right after Table 1).

Shipment# column: unique names of shipments.

Client column: to whom the shipment was sent.

Shipping Type column: can be either FTL or LTL.

Carrier column: who delivered the shipment.

As a result, we need to get 3 tables in the report:

TABLE 1:

Consignors	Shipments (count) Selected month (slicer)	Previous month	Clients (distinct count) Selected month	Previous month	ShippingTypes (distinct count) Selected month	Previous month	Carriers (distinct count) Selected month	Previous month	Status Selected month	Previous month	Points Selected month
Cons_A	100	120	3	1	2	2	2	1	User	Junior	3
Cons_B	30	0	4	0	2	0	2	0	Super Profi	Inactive	8
Cons_C	50	25	4	4	1	2	2	2	Profi	Super Profi	-1

Status for Selected month = 
IF([Selected month clients] = 0, "Inactive,",
    IF([Selected month clients] = 1, "Junior",
        IF([Selected month clients] <= 3, "User",
            IF([Selected month clients] > 3 && [Selected month shipping types] > 1 && [Selected month carriers] >1, "Super Profi",
                IF([Selected month clients] > 3, "Profi",
                BLANK()
                )
            )
        )
    )
)

Status for previous month shoulb be calculated similarly, only instead of [selected month] columns the formula should take [previous month] columns.

Points = [Selected month clients] - [Previous month clients] + [Selected month shipping types] - [Previous month shipping types] + [Selected month carriers] - [Previous month carriers]

TABLE 2: Statuses

Status	Consignors (distinct count) Selected month	Previous month	%	Shipments (count) Selected month	Previous month	%
Inactive	0	1	-100%	0	0	0%
Junior	0	1	-100%	0	120	-100%
User	1	0	100%	100	0	100%
Profi	1	0	100%	50	0	100%
Super Profi	1	1	0%	30	25	20%
Total	3	3	0%	180	145	24%

TABLE 3: New Consignors

Consignors (distinct count) Selected month	Previous month	%	Shipments (count) Selected month	Previous month	%
1	0	100%	30	0	100%

Consignor is new if [Selected month clients] > 0 && [Previous month clients] = 0.

The report should have slicers:

1. selected month
2. status

I am looking for help - how to do it all in DAX?

So far, I created this report with limited functionality - the user is unable to select a month, because my formulas are based on logic "selected month = current month(today)", and "previous month = one month before(today)" which I did in Power Query. In more detail, I created 3 grouped tables from the original table with shipments data:

• for Current month (each Date.IsInCurrentMonth([Calendar]))
• for Previous month (each Date.IsInPreviousMonth([Calendar]))
• for 2 months ago (
  • StartDate = Date.AddMonths(Date.StartOfMonth(DateTime.FixedLocalNow()),-2),
  • EndDate = Date.AddMonths(Date.EndOfMonth(DateTime.FixedLocalNow()),-2),
  • each [Calendar]>=StartDate and [Calendar]<=EndDate)

In each table I had:

• a list of all Consignors
• respective count for [Shipment#] column
• respective distinct count for [Client], [ShippingType], [Carrier]

Then I merged these three tables in one (the key column is a Consignor list). And then I created measures in DAX to calculate the report tables.

But, as I mentioned earlier, this method is limited to currrent calendar month only, while my User wants to select any month in the Slicer.

Somehow I think it would work better if I did not create these separate tables in PowerQuery either, because the report is working soooo slow now... I need somehow to calculate everything only with DAX formulas, just having the original table with shipments data. But I struggle how to do it... Please I need a hint!

[lbendlin · Accepted solution]

- use a data model (keep your tables separate and join them as needed)

- join the transaction table to the calendar table based on the date, not the yearmonth

- use the date (or yearmonth) column fom the calendar table, not from the transaction table

- use measures with PARALLELPERIOD.  use 0 for current month, -1 for previous month etc.

- (optional) use SWITCH() instead of nested IF()