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.
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 |
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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]
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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.
I hope the link works.
I am still looking for a solution.
Any help will be greatly appreciated.
Thanks.
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)
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |