Forum Discussion
Fiscal Year Period YTD with Dynamic Column
Hi All
I am facing challenges while creating YTD along with Dynamic column from Dimension Tables
Task:
I have Fiscal Year Period (Sep-Aug) and randomly i will keep the columns from different dimension tables
Eg: Column_X from Dim_1/Column_Y from Dim2/Column_Z from Dim3...and so on
Issue with output
While calculating YTD under BL_Status (Column_X from Dim1) it is getting split with the categories in BL_Status.
Can you please provide me the solution to avoid the categories split for YTD
For reference i am sending the snapshot along with the desired output
I have tried these approaches, but it didn’t worked
Gross Profit YTD=TOTALYTD ([Gross Profit],'Fiscal Period'[Fiscal Date],ALL ( 'Fiscal Period' ),"31/8")
GP_YTD_Test1=
VAR YTD_ALL=CALCULATE([Gross Profit YTD],ALLSELECTED(),VALUES('Fiscal Period'[Fiscal Date]))
Var YTD_Result= IF(ISBLANK([Gross Profit]), BLANK (), YTD_ALL )
Return YTD_Result
GP_YTD_Test2=
VAR YTD_ALL=CALCULATE([Gross Profit YTD],ALLSELECTED(),VALUES('Fiscal Period'[Fiscal Date]))
Var YTD_Result= IF([Gross Profit]=BLANK()&&[Gross Profit]=[Gross Profit YTD],BLANK(),YTD_ALL)
Return YTD_Result
2 Replies
Syndicate_AdminAdministrator
When you calculate the YTD for the "BL_Status" column, it is divided by the categories in the "BL_Status" column. This is because the "BL_Status" column is not a date column, it's a dimension column, and Power BI doesn't know how to aggregate it correctly.
To fix this issue, you need to create a date hierarchy that corresponds to your fiscal year period (September-August) and use it as the basis for your YTD calculation.
Here are the steps you can take:
Create a new date column in your data source that corresponds to the fiscal year period (September-August).
In Power BI, create a date hierarchy using this new column. You can do this by right-clicking on the new date column and selecting "New hierarchy".
Drag and drop the newly created hierarchy to the "Values" section of your report.
Use the newly created hierarchy as the basis for your YTD calculation: =TOTALYTD(SUM(table[Gross Benefit]),Fiscal_Period[Fecha fiscal],ALL("Fiscal_Period"),"8/31")
Drag and drop the newly created column to the "Values" section of your report.
This way the YTD calculation will be correct, and the BL_Status will not split the categories.
Yes I already have Fiscal Year Table and i have created the same formula and it is working fine. But when i am inserting any categorical column (eg BL Status) it is splitting like I mentioned in 2nd table output.
I need to have 3rd table output
