Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Although it sounds simple, yet I am not able to have the correct values in my table.
I have tested different approcahes such as:
CB Option 1 =CLOSINGBALANCEMONTH(SUM('Table1'[Amount]),'Table1'[Date]) => NOT Returning Correct Ending Balance
CB Option 2 =CLOSINGBALANCEMONTH(SUM('Table1'[Amount]),'DimDate'[Date]) => Returning Correct Ending Balance
However, when I am unselecting January from the filter, Beginning Balance is returing only zeros.
OB Option 1 = OPENINGBALANCEMONTH(SUM('Table1'[Amount]),'DimDate'[Date])
OB Option 2 = [CB] -CALCULATE(SUM(Table1[Amount]),ALL(Table1[Date]))
Also, since my latest selected month is June, I would expect my Ending Balance to remain the same no matter what I am adding/removing prior to that latest month, but it is changing when I am selecting/unselecting anything prior to June. But with CB Option 1 which is not returing the correct amount, the Ending Balance is remaining as expected although with wrong value.
Any help would be appreciated!
@muhitdinfa1 , All-time intelligence function works best with a date table , because it need continuous dates.
OPENINGBALANCEMONTH is CLOSINGBALANCEMONTH of last month
CLOSINGBALANCEMONTH looks for the only end of the month date. but lastdate can last available date.
If this does not help
Can you share sample data and sample output in table format?
refer if these can help
https://www.youtube.com/watch?v=yPQ9UV37LOU
https://www.youtube.com/watch?v=6lzYOXI5wfo
@amitchandak Thank you, but it is not quite helpful. Here's the sample data snapshot. Sometimes, I want to be able to filter by initials in a slicer filter. Unfortunately, it's not allwoing me to paste the values here.
HI @muhitdinfa1,
I'd like to suggest you use the date function to manually define the filter ranges on your table records if you want to achieve some advanced operations.
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |