Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi,
I have 2 datasets:
1. Calendar
2. Table with cash and dates (with different type of dates like, prepayment date, payment, etc)
So, i can't do a relationhsip with the calendar, becasue the relation will be for many columns in the table of cash
To solve that sum of values for each period or type of date, i have created the following measures and they work fine.
Actual Amount using PrePayment Date = CALCULATE(Cash[Actual Amount], USERELATIONSHIP(Cash(CalendarCash[Date],Cash[Actual PrePayment_Date]))
Actual Amount using Payment Date = CALCULATE(Cash[Actual Amount], USERELATIONSHIP(Cash(CalendarCash[Date],Cash[Actual Payment_Date]))
So i have a tablix matrix with calendar dates, and then use one or the other and they work with the same calendar.
The issue is when i try to do accumulated values from that measure that it seems not work
i have tried and it doesnt work.
SUMX (
FILTER (
ALLSELECTED ( Cash[Actual PrePayment_Date] ),
CDB_CashMovements[Actual PrePayment_Date] <= MAX ( CDB_CashMovements[Actual PrePayment_Date] )
),
CDB_CashMovements[Actual Amount using PrePayment Date]
)
or
CALCULATE (
Cash[Actual Amount using PrePayment Date],
FILTER (
ALLSELECTED ( cash[Actual PrePayment_Date],
cash[Actual PrePayment_Date] <= MAX ( cash[Actual PrePayment_Date] )
)
)
but they dont do the accumulated value of cash.
Can someone help me on this?
Regards!
Solved! Go to Solution.
Hi @dobregon ,
According to your description, I create a sample.
Cash table:
CalendarCash table:
The two tables have two inactive relationship as you designed. Here's my solution.
1.I create two measure similar to yours.
Actual Amount using Payment Date =
CALCULATE (
SUM ( Cash[Actual Amount] ),
USERELATIONSHIP ( Cash[Actual Payment_Date], CalendarCash[Date] )
)
Actual Amount using PrePayment Date =
CALCULATE (
SUM ( Cash[Actual Amount] ),
USERELATIONSHIP ( Cash[Actual PrePayment_Date], CalendarCash[Date] )
)
2. I create two accumulate measures.
Accumulate Payment =
IF (
[Actual Amount using Payment Date] = BLANK (),
BLANK (),
SUMX (
FILTER (
ALLSELECTED ( 'CalendarCash'[Date] ),
'CalendarCash'[Date] <= MAX ( 'CalendarCash'[Date] )
),
[Actual Amount using Payment Date]
)
)
Accumulate PrePayment =
IF (
[Actual Amount using PrePayment Date] = BLANK (),
BLANK (),
SUMX (
FILTER (
ALLSELECTED ( 'CalendarCash'[Date] ),
'CalendarCash'[Date] <= MAX ( 'CalendarCash'[Date] )
),
[Actual Amount using PrePayment Date]
)
)
If you want to show all days of the calendar date, you can remove IF statement in the formula, only keep else part. Put Date from CalendarCash table and other measures in a matrix, get the correct result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @dobregon ,
According to your description, I create a sample.
Cash table:
CalendarCash table:
The two tables have two inactive relationship as you designed. Here's my solution.
1.I create two measure similar to yours.
Actual Amount using Payment Date =
CALCULATE (
SUM ( Cash[Actual Amount] ),
USERELATIONSHIP ( Cash[Actual Payment_Date], CalendarCash[Date] )
)
Actual Amount using PrePayment Date =
CALCULATE (
SUM ( Cash[Actual Amount] ),
USERELATIONSHIP ( Cash[Actual PrePayment_Date], CalendarCash[Date] )
)
2. I create two accumulate measures.
Accumulate Payment =
IF (
[Actual Amount using Payment Date] = BLANK (),
BLANK (),
SUMX (
FILTER (
ALLSELECTED ( 'CalendarCash'[Date] ),
'CalendarCash'[Date] <= MAX ( 'CalendarCash'[Date] )
),
[Actual Amount using Payment Date]
)
)
Accumulate PrePayment =
IF (
[Actual Amount using PrePayment Date] = BLANK (),
BLANK (),
SUMX (
FILTER (
ALLSELECTED ( 'CalendarCash'[Date] ),
'CalendarCash'[Date] <= MAX ( 'CalendarCash'[Date] )
),
[Actual Amount using PrePayment Date]
)
)
If you want to show all days of the calendar date, you can remove IF statement in the formula, only keep else part. Put Date from CalendarCash table and other measures in a matrix, get the correct result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yanjiang-msft thanks a lot, that works!!!!!
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
106 | |
68 | |
48 | |
44 | |
42 |