How to create a measure, which shows me remaining values by months

[Jakub_Poweska11]

Hello All,

I am strugling with creating a measure, which shows me for particular months what values we have still to remain. Simple, substracting values from previous period at the begginning and later from values what we already have next value from next month. Here is a sample data:

Month	High	Remaining
1/1/2025	87	87
2/1/2025	27	60
3/1/2025	20	40
4/1/2025	0	0

In column Remaining is result, what i want to achieve. I dont have column Remaining in data, this is a RESULT to achieve

Thank you in advance

[AntrikshSharma]

@Jakub_Poweska11 

Total High = SUM ( 'Table'[High] )

Remaining High = 
VAR HighByMonth =
    ADDCOLUMNS ( 
        ALL ( 'Table'[Month] ), 
        "@High", [Total High] 
    )
VAR FirstMonthHigh =
    SELECTCOLUMNS (
        INDEX ( 1, HighByMonth, ORDERBY ( 'Table'[Month], ASC ) ),
        [@High]
    )
VAR RollingWindow =
    WINDOW ( 
        2, ABS, 0, REL,
        HighByMonth,
        ORDERBY ( 'Table'[Month], ASC )
    )
VAR RunningTotal =
    SUMX ( RollingWindow, [@High] )
VAR Result =
    IF (
        [Total High] = 0 || NOT ISINSCOPE ( 'Table'[Month] ),
        0,
        FirstMonthHigh - RunningTotal
    )
RETURN
    Result

[AntrikshSharma]

@Jakub_Poweska11 I would prefer to do this in Power Query itself.

let
    Source = Table,
    Acc = List.Accumulate (
        List.Skip ( Source[High] ),
        { List.First ( Source[High] ) },
        ( s, c ) => s & { if c = 0 then 0 else List.Last ( s ) - c }
    ),
    Combine = Table.FromColumns (
        Table.ToColumns ( Source ) & { Acc },
        type table [ Month = date, High = number, Remaining = number ]
    )
in
    Combine

[sanalytics · Accepted solution]

@Jakub_Poweska11 
Please create a measure based on below logic.
1) Create First Month High of all the Month.. That should be 87 for all the Month
2) Create Running total without taking the first  Month..
3) Then Differentiate it..
Now, i have one question why the last month is showing 0, if the last month high is 0 then remainig should be 40.. If it is by default 0 then below is the code

Remianing = 
VAR _FirstMonth = 
MONTH(
MINX(
 ALL('Table'[Month] ),
 [Month]
) )
VAR _LastMonth = 
MONTH(
MAXX(
 ALL('Table'[Month] ),
 [Month]
) )
VAR _FMH = 
CALCULATE(
    SUM( 'Table'[High] ),
FILTER( ALL( 'Table'),
MONTH( 'Table'[Month] ) = _FirstMonth
) )
VAR _MonthNumber = 
MONTH( MAX( 'Table'[Month] ) )
VAR _RT = 
CALCULATE(
    SUM( 'Table'[High] ),
FILTER( ALL( 'Table'),
MONTH( 'Table'[Month] ) > _FirstMonth &&
 MONTH( 'Table'[Month] ) <= _MonthNumber 
) )
VAR _Result = 
IF(
 MONTH( MAX( 'Table'[Month] )) = _FirstMonth,
 SUM( 'Table'[High] ),
 IF(
MONTH( MAX( 'Table'[Month])) = _LastMonth,0,
 _FMH - _RT
) )
RETURN
_Result

Just remove the last month logic from If statement if you want to show as 40..
I ahve tested with some other month and this is returning correct figure.. Below is the screenshot and complete file.

I must say this is very interesting problem, had to open power bi desktop and code. Thanks.. 

Hope this helps

Regards

sanalytics

[Jakub_Poweska11]

One Question, what should i change if i want to use Month - Year (format Jan 2025 etc) from Calendar table. I have connection between tables Calendar Date and Month in Table. Changed to: 

Remaining High =

VAR _FirstMonth =

MONTH(

MINX(

 ALLSELECTED('Calendar'[Date] ),

 [Date]

) )

VAR _LastMonth =

MONTH(

MAXX(

 ALLSELECTED('Calendar'[Date] ),

 [Date]

) )

VAR _FMH =

CALCULATE(

    SUM( 'Secret Findings ITCC'[High] ),

FILTER( ALLSELECTED( 'Secret Findings ITCC'),

MONTH( 'Secret Findings ITCC'[Month] ) = _FirstMonth

) )

VAR _MonthNumber =

MONTH( MAX( 'Secret Findings ITCC'[Month] ) )

VAR _RT =

CALCULATE(

    SUM( 'Secret Findings ITCC'[High] ),

FILTER( ALLSELECTED( 'Secret Findings ITCC'),

MONTH( 'Secret Findings ITCC'[Month] ) > _FirstMonth &&

 MONTH( 'Secret Findings ITCC'[Month] ) <= _MonthNumber

) )

VAR _Result =

IF(

 MONTH( MAX( 'Secret Findings ITCC'[Month] )) = _FirstMonth,

 SUM( 'Secret Findings ITCC'[High] ),

 IF(

MONTH( MAX( 'Secret Findings ITCC'[Month])) = _LastMonth,0,

 _FMH - _RT

) )

RETURN

_Result

and its not working

[sanalytics]

@Jakub_Poweska11 
Requesting you to provide pbix file along with your data model.. Need to debugg why this not working..
As per your data shared.. it is working fine and showing expected output.

Regards,

sanalytics

[Jakub_Poweska11]

I cant share my file. I have in Calendar[Date] whole days for month and you?

[Jakub_Poweska11]

Thank you so much. Its working.

Requirement is that, when High is 0 it means that we removed all values

[bhanu_gautam]

@Jakub_Poweska11 Go to the "Modeling" tab.
Click on "New Column".
Enter the following DAX formula:

dax
Remaining =
VAR CurrentMonth = 'Table'[Month]
VAR CurrentHigh = 'Table'[High]
VAR PreviousRemaining =
CALCULATE(
MAX('Table'[Remaining]),
FILTER(
'Table',
'Table'[Month] < CurrentMonth
)
)
RETURN
IF(
ISBLANK(PreviousRemaining),
CurrentHigh,
PreviousRemaining - CurrentHigh
)

[Jakub_Poweska11]

@bhanu_gautam 
Sorry, but i dont have column Remaining in my data. I wrote in my first message that is a result, which i want to achieve. That was result example