Unable to get the YTD number for a given total
I'm working with a trial balance which gives output in YTD format i.e. it rolls up the numbers from Jan to whichever month you are downloading the data for. Now I want to work our the amounts for the month for which I have written the below dax Below is my Base data named "OS TB_Input" which is granular to the GL and Cost Centre and I have a Dimention table for both GL and Cost centre separately and mapped to this Fact table along with a Calendar table mapped to the Month Column. Amount is the column with YTD numbers for each particular month for that GL/ Cost Centre. "[Trial Balance]" just sums up the amount column which is YTD numbers. Trial Balance = SUM('OS TB_Input'[Amount]) Monthly is a measure created to find the monthly values Monthly = VAR PrevMonth = CALCULATE( [Trial Balance], PREVIOUSMONTH('Calendar'[Date]), COA_Grouping[P&L/ BS] = "P&L" ) VAR CurrentMonth = CALCULATE( [Trial Balance], COA_Grouping[P&L/ BS] = "P&L" ) RETURN IF( ISBLANK(CurrentMonth), BLANK(), [Trial Balance]- PrevMonth ) Although this gives the monthly numbers correctly but it messes up the totals as what I expect in the total yearly column is the sum of all the Months whereas it's showing up a much different number. Pl help to correct the totals and also if someone could suggest how to improve the dax, that would be of great help.YTD filter based on the selected month
Hello, I'm trying to create a dynamic time filter based on the selected month (Always one month selected in the report): MTD: Selected month YTD: Janv - to selected month (No cumulation !) FY: Janv - Dec First I tried with calculation group but I always get cumulated YTD result. Now I try with a DAX table but I can't link the selected month in the DAX table... Model: DAX Filter table: MonthFilter = Var _SelectedMonth= max(SelectedMonth[SelectedMonth]) Var _MonthFilter = UNION( ADDCOLUMNS( SELECTCOLUMNS(GENERATESERIES ( _SelectedMonth,_SelectedMonth,1 ) ,"Mois",INT([Value])) ,"MonthFilter","MTD" ,"Order",1) ,ADDCOLUMNS( SELECTCOLUMNS(GENERATESERIES ( 1,_SelectedMonth,1 ) ,"Mois",INT([Value])) ,"MonthFilter","YTD" ,"Order",2) ,ADDCOLUMNS( SELECTCOLUMNS(GENERATESERIES ( 1, 12 ,1 ) ,"Mois",INT([Value])) ,"MonthFilter","Full Year" ,"Order",3) ) Return _MonthFilter Result: Thanks for your help 🙂
Recursion/Self referencing & year-to-date attribution effect
Hello Fabric Community, I hope you're all doing well! I'm currently working on a project where I need to calculate the year-to-date cumulative attribution effect for investment portfolio analysis in Power BI, and I've encountered a bit of a roadblock. Here's a breakdown of the problem: I have a formula that calculates the Year-to-date cumulative attribution effect: Ei' = (Ei-1')(1+BRi/100) + (Ei)(1+PRi-1'/100) where: Ei' is the cumulative attribution effect through period i. Ei-1' is the cumulative attribution effect in period i-1. BRi is the benchmark return in period i. Ei is the attribution effect in period i. PRi-1' is the cumulative portfolio return through period i-1. Note that on the 1st of January no historical data should be used in this formula (even though the dataset contains data from multiple years): Ei' = (0)(1+BRi/100) + (Ei)(1+0/100) Thus on January 1st: Ei' = Ei I find it difficult to implement the logic correctly, particularly the self-referencing. I read in other posts that a work-around for self-referencing in formulas is to make the formula closed-form. Could anyone provide guidance on the possibility of making this formula closed form and if possible, how to write a code for this formula that works in DAX? Below you find some sample data and in column G the excel calculation for this formula. A B C D E F G 1 Date Port. Total Return Cumu. Port. Total Return Bench. Total Return Attribution effect Cumulative attribution effect Excel formula 2 1-1-2024 -0,282 -0,282 -0,222 -0,001 -0,001366 ‘=E2 3 2-1-2024 -0,13 -0,411 -0,176 0,0163 0,014863917 ‘=F2*(1+D3/100)+E3*(1+C2/100) 4 3-1-2024 -0,494 -0,903 -0,496 -0,023 -0,00787653 ‘=F3*(1+D4/100)+E4*(1+C3/100) 5 4-1-2024 -0,177 -1,078 -0,181 0,0359 0,02771068 ‘=F4*(1+D5/100)+E5*(1+C4/100) 6 5-1-2024 -0,02 -1,098 -0,003 -0,02 0,00814329 ‘=F5*(1+D6/100)+E6*(1+C5/100) 7 6-1-2024 -0,252 -1,347 -0,222 -0,023 -0,01474788 ‘=F6*(1+D7/100)+E7*(1+C6/100) Note that column G contains the excel formulas I use to calculate column F. I want to replicate the results in column F by dax code. Any insights or suggestions would be greatly appreciated! Thank you in advance for your help.
Automatically determine which fiscal months are YTD across years
Below is a sample from my Date table. Our fiscal year runs April - March. I have 4 years worth of data. My data is in the "Sales Month" format, I do not receive actual days, just the month in that format. When I loaded my August data a few days ago, my YTD for this year and prior years would be April - August. When I did this in Excel I would add a column to my date table and each month I would add a Y to the new month in all 4 years that designated YTD and I would filter on it. Is there a way to do something like this, but automatically based on the last sales month loaded? TYIA.
Calculate YTD and YTD-1 for the same period
Hello, I would like to calculate the YTD Spend and the YTD -1 Spend for the same period. For example for 2021 we are in October so I would like to calculate the Spend for 2020 only until October. For the moment I have created this formula : Spend YTD-1 = CALCULATE(SUM('Spend consolidé'[Invoice_Accounting_EUR]),SAMEPERIODLASTYEAR('Calendar'[Date])) But it's not working, when I filter on 2021, with this formula I have the spend for the entire year of 2020 instead of having until October. Any advice to help me solve my problem ? Thanks, Axel
YTD MEASURE NOT WORKING AS INTENDED
Here is the Table named test: Here is the table 2 which contains Dates, which is used as Slicer: Dates = CALENDAR(date(2022,1,1),TODAY()) Link between Dates and test dataset: Question: Create a Measure that shows the YTD Total amount for the current year, even if the date slicer starts from the previous year: Here is my code: YTD = VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date])) var startyear = DATE(YEAR(TODAY()),1,1) return if( minim >= startyear , calculate(sum(test[Totals] ), FILTER(Dates, Dates[Date] >= startyear)) , calculate(sum(test[Totals] ), FILTER(Dates, Dates[Date] >= startyear)) ) Test Scenario 1 Works fine: Shows correct info, the date slicer starts at 2022, and the measure shows the YTD result of current year Test Scenario 2: Does Not Work, Shows 40 instead of 130 (Expected output 130) Test Scenario 3: Does not work, Even when the year is the current year, (Expected output 130)
