Hi there,
I have a dataset where the values are in YTD. I want to convert the YTD values into Monthly values.
I need the monthly values calculated in my dataset.
I have attached the dataset with the desired result (Monthly values) highlighted in yellow. The calculation should take into account the Product, entity, and date.
Thanks,
Rohit
A Caclulated column is probably the way to go here, at least in my view. First thing I did was create a Index column so I have an idea what the previous value is. I didnt want to assume it was always just the previous month.
CurrentRank =
/*Set Variables based on the current row context
We want to "Keep" the Line, Product, Entity and Date values*/
VAR _CurrentLine = 'Table'[LINE]
VAR _CurrentProduct = 'Table'[Product]
VAR _CurrentEntity = 'Table'[Entity]
VAR _CurrentDate = 'Table'[Date]
/*Going to use RANKX in order to have context of what the "previous row" is*/
VAR _CurrentRank =
RANKX(
FILTER(
'Table'
,_CurrentLine = 'Table'[LINE]
&& _CurrentProduct = 'Table'[Product]
&& _CurrentEntity = 'Table'[Entity]
)
,'Table'[Date]
,
,ASC
,Dense
)
RETURN _CurrentRank
Which would yield this column
Then we can leverage that to get the monthly value
Monthly Value CaclColumn =
/*Set varaibles, since the data resets every year, we need to be sure we know what year we are in*/
VAR _CurrentLine = 'Table'[LINE]
VAR _CurrentProduct = 'Table'[Product]
VAR _CurrentEntity = 'Table'[Entity]
VAR _CurrentDate = 'Table'[Date]
VAR _CurrentRank = 'Table'[CurrentRank]
VAR _CurrentYear = YEAR( 'Table'[Date] )
/*This is finding the previous value (based on our Current Rank Column) for the Current Product,
Line, Entity, current year*/
VAR _PrevValue =
CALCULATE(
MAX( 'Table'[YTD Value] )
,FILTER(
ALL('Table')
,_CurrentLine = 'Table'[LINE]
&& _CurrentProduct = 'Table'[Product]
&& _CurrentEntity = 'Table'[Entity]
&& _CurrentYear = YEAR('Table'[Date] )
&& _CurrentRank = 'Table'[CurrentRank] + 1
)
)
/*The current value in the current row*/
VAR _CurrentYTD = 'Table'[YTD Value]
/*Subtract the previous YTD Value and the Current Row Value*/
VAR _MonthlyValue = _CurrentYTD - _PrevValue
RETURN
/*if current rank =1, which means first month of the year, we dont want to "go back" we want that value
else, go back one and get that value*/
IF( _CurrentRank= 1, _CurrentYTD, _MonthlyValue)
Probably easier to do the Index column in Power Query, but didnt want to assume that was an option. Just be sure to hide that CurrentRank column because it’s not needed anywhere else. Also be sure to set the Date column to Date data type.
Here’s the pbix file
Convert YTD to Monthly Data Points.pbix (23.9 KB)
Hi @rohit,
You can try this too.
Create Calculated Column
Year = YEAR('Table'[Date])
RANKing =
RANKX (
FILTER (
'Table',
'Table'[Entity]
= EARLIER ( 'Table'[Entity] )
&& 'Table'[Year]
= EARLIER ( 'Table'[Year] )
&& 'Table'[Product]
= EARLIER ( 'Table'[Product] )
),
'Table'[YTD Value]
)
Column =
VAR __a =
CALCULATE (
MAX ( 'Table'[YTD Value] ),
FILTER (
'Table',
'Table'[Year]
= EARLIER ( 'Table'[Year] )
&& 'Table'[Product]
= EARLIER ( 'Table'[Product] )
&& 'Table'[Entity]
= EARLIER ( 'Table'[Entity] )
&& 'Table'[RANKing]
= EARLIER ( 'Table'[RANKing] ) + 1
)
)
RETURN
'Table'[YTD Value] - __a
Regards,
Harsh Nathani
