topic Re: How to create a measure, which shows me remaining values by months in DAX Commands and Tips

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

Jakub_Poweska11 — Fri, 25 Apr 2025 08:23:12 GMT

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

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

bhanu_gautam — Fri, 25 Apr 2025 08:14:47 GMT

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
)

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

Jakub_Poweska11 — Fri, 25 Apr 2025 08:22:25 GMT

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

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

sanalytics — Fri, 25 Apr 2025 09:16:56 GMT

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

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

Jakub_Poweska11 — Fri, 25 Apr 2025 09:39:57 GMT

Thank you so much. Its working.

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

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

Jakub_Poweska11 — Fri, 25 Apr 2025 09:44:08 GMT

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

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

sanalytics — Fri, 25 Apr 2025 10:17:30 GMT

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

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

Jakub_Poweska11 — Fri, 25 Apr 2025 10:46:13 GMT

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

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

AntrikshSharma — Fri, 25 Apr 2025 12:29:13 GMT

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

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

AntrikshSharma — Fri, 25 Apr 2025 12:39:16 GMT

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