How to convert YTD values to Monthly values

[sunag]

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 in my dataset.

Sample data:

Month	Values YTD	Item
Jan	10	Sales
Feb	20	Sales
Mar	30	Sales
Apr	40	Sales
May	60	Sales
Jun	80	Sales
Jul	90	Sales
Aug	100	Sales
Sep	120	Sales
Oct	135	Sales
Nov	145	Sales
Dec	150	Sales

[kimjoa]

Hi, I have trial balances for jan, feb, mar combined in a table, with columns for ledger account, period, YTD balance etc.

In order to be able to switch between YTD and Periodic figures I have calculated the periodic values in two steps:

1)  

Closing Amount Previous Month = CALCULATE(SUM('Trial Balance'[Closing Amount]), DATEADD('Calendar'[Date],-1,MONTH))

and then 

2) 

TB Periodic = SUM('Trial Balance'[Closing Amount]) - [Closing Amount Previous Month]

The problem is that it calculates periodic values one month ahead from my current Trial Balance dataset period. 

Any idea on how to avoid the april calculation with a filter or max function etc.?

[Fowmy · Accepted solution]

@sunag 

Hi, Add this as a new column to your table. I check with your variance and it matches. 

Monthly Amount New = 
VAR _PDATE = 

CALCULATE(
    MAX('dataset'[Date]),
    CALCULATETABLE(
        ALLSELECTED('dataset'), 
        'dataset'[Product]=('dataset'[Product]),
        'dataset'[Entity] = ('dataset'[Entity]),
        'dataset'[Date]< EARLIER('dataset'[Date])
    )
)

VAR _PYTD = 

CALCULATE( MAX('dataset'[YTD Value]),
    CALCULATETABLE(
        ALLSELECTED('dataset'), 
        'dataset'[Product]=EARLIER('dataset'[Product]),
        'dataset'[Entity] = EARLIER('dataset'[Entity]),
        'dataset'[Date] =  _PDATE
    )
)

VAR YR =YEAR(_PDATE)
VAR CYR =YEAR('dataset'[Date])
RETURN
IF(
    YR < CYR,
    [YTD Value],
    [YTD Value] - _PYTD
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[sunag]

@Fowmy  Thanks, it works. 

However, i got into another issue. I merged this table with another table that also has monthly values for othet expense items. As the monthly value in dataset table is a calculated column, the monthly amount shows blank when merged with other table. 

Any solution for this?

Thanks in advance.

[Fowmy]

@sunag 

As per your original question to calculate the monthly amount from YTD is done and it works, great!.
What you are having now is a different merge issue. You can open a new question to resolve that and accept the solution I provided.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[vivran22]

Hello @sunag 

You may try this:

//If you have column for months
Monthly Values =
VAR _CurrentValue = dtTable2[Values YTD]
VAR _CurrentMonth = dtTable2[Month]
VAR _PrevMonth =
    EOMONTH ( _CurrentMonth, -2 ) + 1
VAR _PrevValue =
    LOOKUPVALUE ( dtTable2[Values YTD], dtTable2[Month], _PrevMonth )
VAR _Difference = _CurrentValue - _PrevValue
RETURN
    _Difference


//In case there is no month column
Monthly Values =
VAR _CurrentValue = dtTable[Values YTD]
VAR _CurrentIndex = dtTable[Index]
VAR _PrevIndex = _CurrentIndex - 1
VAR _PrevValue =
    LOOKUPVALUE ( dtTable[Values YTD], dtTable[Index], _PrevIndex )
VAR _Difference = _CurrentValue - _PrevValue
RETURN
    _Difference

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[sunag]

Hi Vivek,@vivran22 

Thanks.

I have attached the dataset with the desired result (Monthly values) highlighted in yellow.  The calcution should take into account the Product, entity and date.

Link to dataset 

I hope the link works.

[sunag]

I am still looking for a solution.

Any help will be greatly appreciated.

Thanks.

[harshnathani]

Hi @sunag ,

Create Columns in your table

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

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)