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 )
Dax for start quarter week in fiscal year to selected week
I have relative week numbers, and through scliers I have selected relative week. I want total sum of weeks from first week of quarter to selected week. Please consider this is a fiscal week from Feb to Feb, and data is aggregate on weekly there is no any calendar table.
How to calculate QTD and YTD based on MTD
Hello, I have a table with MTD value calculated everyday by ETL team. as a report developer, how to calcuate QTD and YTD by using the MTD value? There is no daily value in the fact table. Date MTD QTD YTD 2022-01-01 5 2022-01-02 8 ..... 2022-01-30 100 2022-01-31 110 2022-02-01 2 2022-02-02 5 .... 2022-02-27 70 2022-02-28 76 ..... 2022-03-01 7 2022-03-02 11 ....
Dax formula for "4 Weeks to Date" (I have 1WTD)
Hi there. In the first image below I have plotted "One Week to Date (1WTD)" sales and "Month to Date (MTD)" sales. I would like to replace MTD with "4 weeks to date (4WTD)". I would then get a consistent pattern as illustrated in image 2 (where I hand drew 4WTD). Question: Could someone please help me with the DAX code for "4WTD" similar to my 1WTD formula? Dataset & .pbix: here (dataset from Enterprise DNA's Ultimate Beginners Guide to Dax) 1WTD formula: (formula from RADCAD's tutorial ) As always, thank you. MichelleQuarter to date variance
Hi guys, I have a fact table with account balances and a dim date that contains date and previous quarter end for each date. Both tables contain info about working days only. I would like to prepare a report that returns the % variance QtD of the accounts balance sum based on the date selected in a slicer. For example the sum of the balances is 12 usd on 5 Apr 2019, the sum of the balances is 10 usd on 29 Mar 2019 (end of previous Q) and the report should return 20%. If I change the slicer value to 4 Apr then the report should compare the sum from 4 Apr to the sum of 29 Mar. Consequently if I choose 12 Jul 2019 it should compare with 30 Jun. How can I achieve this please? Many thanks, Sabin
Strange behaviour with YTD calculations
Hi, I'm trying to do some YTD calculations in one of my summaries. I did it in 2 different ways, each came with its own problem: 1. At first, I tried using the TotalYTD function: YTD Sales Alt = TOTALYTD(sum(SalesHistoryData[Sales]),DATESYTD(CalendarTable[Date],"30/06")) I know the format string is dependent on the local setting of your machine, but my machine setting is exactly that "dd/mm", but somehow that formular doesn't work. It does the YTD sum, but it runs from Jan to Dec rather than Jul to Jun as I told it to. 2. And I know that format string is correct because I tried it with a different formula: YTD Sales = Calculate(sum(SalesHistoryData[Sales]),DATESBETWEEN(CalendarTable[Date],STARTOFYEAR(CalendarTable[Date],"30/06"),LASTDATE(CalendarTable[Date]))) That gives to expected behaviour in terms of summing from Jul to Jun. However, it also gives a sum of everything as well: (there are no blanks in my calendar table and here's the summary without putting the 2nd measure): (but as soon as I put in the 2nd measure, suddenly there is a blank in the summary) Can I get some help please so that I can understand what is happening: 1. How come the format string worked in the 2nd formula but not the 1st? 2. Why did the 2nd formula summarise the total for the whole dataset against a "blank" date that doesn't exist? Also, I thought these 2 formulas should be an exact substitute for each other (or at least they should behave in the same way), but apparently not. Can anyone please help by pointing out why they are behaving differently, should they be doing the same thing? Thanks. Tam.
