Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 🙂
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.
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.
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.
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?
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:
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |