PYTD / YOY QTD formula issue
I have data that is only giving a quarterly date so the typical PYTD calculation is not working due to the time intelligence. I am trying to use this formula below but am getting an error saying "STARTOFQUARTER" is not of type DATE" What can I do from here? Or can anyone point out an issue with this formula or provide a better functioning formula? YoY PQTD = // Current Quarter To Date VAR CurrentQTD = CALCULATE ( SUM (OWSSummaryByQuarterByEU[channel_licenses_net_added] ), // Filter for the current quarter FILTER ( OWSSummaryByQuarterByEu, OWSSummaryByQuarterByEu[quarter].[Date]>= STARTOFQUARTER('EU Report'[Today]) && OWSSummaryByQuarterByEu[quarter].[Date] <= TODAY() ) ) // Previous Year's Same Quarter To Date VAR PreviousYearQTD = CALCULATE ( SUM (OWSSummaryByQuarterByEU[channel_licenses_net_added]), // Filter for the same quarter of the previous year FILTER ( OWSSummaryByQuarterByEu, OWSSummaryByQuarterByEu[quarter].[Date] >= STARTOFQUARTER ( DATEADD ( 'EU Report'[Today], -1, YEAR ) ) && OWSSummaryByQuarterByEu[quarter].[Date] <= DATEADD ( 'EU Report'[Today], -1, YEAR ) ) ) // Return the YoY difference RETURN ( CurrentQTD - PreviousYearQTD ) / ABS ( PreviousYearQTD )
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 🙂
