Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
This is my table, I want a customer's closing stock value for a given month to be assigned to the initial stock for the following month.
example: 12/31/2020 value is 18079, I want this to appear on 1/4/2021, but what I have is 34984.
Is there any way to do this, pls help your urgent
Solved! Go to Solution.
Hi @Syndicate_Admin,
You can try to use the following measure expression if it meets your requirement:
Measure =
VAR currDate =
MAX ( Table[Date] )
VAR minDateCM =
//min date of current month
CALCULATE (
MIN ( Table[Date] ),
FILTER (
ALLSELECTED ( Table ),
YEAR ( [Date] ) = YEAR ( currDate )
&& MONTH ( [Date] ) = MONTH ( currDate )
)
)
VAR maxDatePM =
//max date of previous month
CALCULATE (
MAX ( Table[Date] ),
FILTER (
ALLSELECTED ( Table ),
[Date] < DATE ( YEAR ( currDate ), MONTH ( currDate ), 1 )
)
)
RETURN
IF (
currDate = minDateCM,
LOOKUPVALUE ( Table[Value], Table[Date], maxDatePM ),
SUM ( Table[Value] )
)
If the above not help, please share some dummy data with raw schema and expected result to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @Syndicate_Admin,
You can try to use the following measure expression if it meets your requirement:
Measure =
VAR currDate =
MAX ( Table[Date] )
VAR minDateCM =
//min date of current month
CALCULATE (
MIN ( Table[Date] ),
FILTER (
ALLSELECTED ( Table ),
YEAR ( [Date] ) = YEAR ( currDate )
&& MONTH ( [Date] ) = MONTH ( currDate )
)
)
VAR maxDatePM =
//max date of previous month
CALCULATE (
MAX ( Table[Date] ),
FILTER (
ALLSELECTED ( Table ),
[Date] < DATE ( YEAR ( currDate ), MONTH ( currDate ), 1 )
)
)
RETURN
IF (
currDate = minDateCM,
LOOKUPVALUE ( Table[Value], Table[Date], maxDatePM ),
SUM ( Table[Value] )
)
If the above not help, please share some dummy data with raw schema and expected result to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
With help from the date table, try openingbalancemonth
openingbalancemonth(Sum('Table'[Value]), Date[Date])
refer my video : https://youtu.be/6lzYOXI5wfo?t=117
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |