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.
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
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 |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |