DAX: Show measure total on first of month, no matter the selected period

[Titatovenaar2]

I have a Fact table that is connected to the Calendar table. In the Fact table I have two facts that are unioned.

One fact has the purchase orders with in column 'total' the amounts per purchase order.

The other fact has the general ledger with in column 'total' the amounts per voucher.

The purchase order fact doesn't have any voucher details and the general ledger fact doesnt have any purchase order information.

This means the Fact currently looks like the following:

subject, date, amount, voucher, purchaseorder

purchaseorders, 2023-10-25, 800, NULL, INK1001

purchaseorders, 2023-02-05, 320, NULL, INK1002

purchaseorders, 2022-11-30, 460, NULL, INK1003

generalledger, 2023-09-20, 300, B5003, NULL

generalledger, 2023-07-20, 200, B5004, NULL

generalledger, 2022-02-20, 500, B5005, NULL

Now in PowerBI I want a matrix that shows the purchase orders and vouchers with their corresponding amounts per date, but I want the purchase orders to ignore the date, I want their amounts to always show up. So in the purchase order measure I ignored the Calendar via ALL('Calendar'), but now the purchase order amount is repeated on every single day of the month. It currently looks like this:

Matrix in PowerBI (with year 2023 selected and month July selected):

Voucher, Purchase Order, Date, msrPurchaseOrderAmount, msrVoucherAmount

NULL, INK1001, 2023-07-01, 800, NULL

NULL, INK1001, 2023-07-02, 800, NULL

NULL, INK1001, 2023-07-03, 800, NULL ... (all the way until 2023-07-31, for every purchase order)

B5004, NULL, 2023-07-20, NULL, 200

But what I want is:

Matrix in PowerBI (with year 2023 selected and month July selected):

Voucher, Purchase Order, Date, msrPurchaseOrderAmount, msrVoucherAmount

NULL, INK1001, 2023-07-01, 800, NULL

NULL, INK1002, 2023-07-01, 320, NULL

NULL, INK1003, 2023-07-01, 460, NULL

B5004, NULL, 2023-07-20, NULL, 200

Measure msrPurchaseOrderAmount:

CALCULATE( 
SUM(Fact[Total])
,Subject = "purchaseorders"
,ALL(Calendar)
)

Measure msrVoucherAmount:

CALCULATE( 
SUM(Fact[Total])
,Subject = "generalledger"
)

So I want the purchase orders to be shown in the same table but then on the first date of the selected period. How can this be done? Any suggestions are very welcome 🙂

[Anonymous]

Hi @Titatovenaar2 ,

Please have a try.

msrPurchaseOrderAmount = 
VAR _FirstDate = FIRSTDATE ( Calendar[Date] )
RETURN
CALCULATE ( 
    SUM ( Fact[Total] ),
    Subject = "purchaseorders",
    ALL ( Calendar ),
    Calendar[Date] = _FirstDate
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[123abc]

Create a new measure for msrFirstDatePurchaseOrderAmount:

msrFirstDatePurchaseOrderAmount =
VAR SelectedStartDate = MIN('Calendar'[Date])
RETURN
CALCULATE(
SUM(Fact[Total]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] = SelectedStartDate
),
Fact[Subject] = "purchaseorders"
)

This measure calculates the sum of purchase order amounts but filters it to only include the amount on the first date of the selected period.

1. Modify your existing msrPurchaseOrderAmount measure to exclude the filter on the Calendar table:

msrPurchaseOrderAmount =
CALCULATE(
SUM(Fact[Total]),
Fact[Subject] = "purchaseorders"
)

Now, your matrix will display the purchase order amounts on the first date of the selected period and voucher amounts on their respective dates as desired.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Titatovenaar2]

Thanks for your fast reply (and sorry for my late one),

When I tried your measure, I see indeed only the value for certain specific dates, which is a good thing. However, there is no total being shown anymore.

Also when I move the hierarchy of the matrix up one level (and thus removing the 'date' column), because it doesn't summarize the individual records, it leaves an entirely blank record in the matrix (or table).
Any idea how to circumvent this?

Your measure I used:

msrFirstDatePurchaseOrderAmount =
VAR SelectedStartDate = MIN('Calendar'[Date])
RETURN
CALCULATE(
	SUM(Fact[Total]),
	FILTER(
		ALL('Calendar'),
		'Calendar'[Date] = SelectedStartDate
	),
	Fact[Subject] = "purchaseorders"
)

I tried to force the context via SUMX and SUMMARIZE to the exact same context of the table, but to no avail, it still refuses to summarize the totals.

msrFirstDatePurchaseOrderAmount =
VAR SelectedStartDate = MIN('Calendar'[Date])
RETURN
SUMX(
	SUMMARIZE(
		Fact,
		Calendar[Date],
		'DIM PurchaseOrder'[Purchase Order],
		'DIM Voucher'[Voucher],
		"msr",
			CALCULATE(
				SUM(Fact[Total]),
				FILTER(
					ALL('Calendar'),
					'Calendar'[Date] = SelectedStartDate
				),
				Fact[Subject] = "purchaseorders"
			)
		)
	),
	[msr]
)

Any ideas on why this is happening?

[123abc]

I see your point regarding the total not being displayed and the issue with the blank record when you remove the 'date' column from the matrix. To address these concerns, you can use a different approach by creating a separate measure to calculate the total for purchase orders. Here's how you can modify your measures:

1. Keep your msrFirstDatePurchaseOrderAmount measure as you originally had it:

msrFirstDatePurchaseOrderAmount =
VAR SelectedStartDate = MIN('Calendar'[Date])
RETURN
CALCULATE(
SUM(Fact[Total]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] = SelectedStartDate
),
Fact[Subject] = "purchaseorders"
)

Create a new measure for the total of purchase orders without any date filtering:

msrTotalPurchaseOrderAmount =
CALCULATE(
SUM(Fact[Total]),
Fact[Subject] = "purchaseorders"
)

Now, you can use msrFirstDatePurchaseOrderAmount for the first date of the selected period, and msrTotalPurchaseOrderAmount for the total. This way, you can have both the first date value and the total in your matrix.

Regarding the issue with the blank record when you remove the 'date' column, you can create a measure to hide the blank records. Here's how you can do that:

msrVoucherAmountNonBlank =
IF(ISBLANK([msrVoucherAmount]), 0, [msrVoucherAmount])

This measure will replace any blank values with zero, ensuring that you don't see blank records in your matrix.

With these measures in place, your matrix should display the purchase order amounts correctly for both the first date of the selected period and the total, and the blank records issue should be resolved.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.