Incorrect Total in Matrix - Trying to Produce Average in the Total
Thank you in advance for the help, so I have the below DAX measure that is technically producing the correct values for each month, however my total is incorrect. I want my total to produce the average of all the months in 2023 below. So the correct average would be $18.65 instead of $26.58 (per below screenshot). How can I change my measure to do this? I believe I need an AVERAGEX or a HASONEVALUE function, but not sure about the proper syntax. WTI - WCS Diff ($US/bbl) - Hardisty = VAR LastDayWithPrice = CALCULATE( LASTNONBLANKVALUE(Data[PriceDate], MAX(Data[PriceDate])), Data[symbol]="#DG.MO_INDEX.WCS_HRD/CURVE/M00") VAR Price = CALCULATE( [Average Close], Data[PriceDate]=LastDayWithPrice, Data[symbol]="#DG.MO_INDEX.WCS_HRD/CURVE/M00") RETURN ABS(Price) Matrix Visual (total is incorrect, monthly values are correct). I want the total to be an average of the monthly values. Fields in Matrix:
Average total by day-number of each month for a measured total.
Hi, I have a dataset of starters , with their true start date, and also the date that they paid a deposit. e.g. Start Date Deposit Date Calendar Month Starter 2022-06-02 00:00:00 2022-06-02 05:00:25.7480000 2022-06-01 00:00:00 1 2022-06-02 00:00:00 2022-05-26 09:15:02.4466667 2022-06-01 00:00:00 1 2022-06-02 00:00:00 2022-05-24 08:23:22.0766667 2022-06-01 00:00:00 1 2022-05-26 00:00:00 2022-05-11 14:06:23.9666667 2022-05-01 00:00:00 1 2022-05-26 00:00:00 2022-05-13 12:46:03.2166667 2022-05-01 00:00:00 1 2022-05-26 00:00:00 2022-05-20 09:10:57.9300000 2022-05-01 00:00:00 1 I use the following DAX to calculate, from each date , how many starters are 'known about' in the month from how many deposits are in the system as of that date which also stops data returning from the current date onwards, and accounts for starters on the day into the totals: Starter Total = IF( MAX('dim_DateReference'[Date]) > TODAY(), BLANK(), IF(MAX('dim_DateReference'[Date]) = TODAY(), CALCULATE(COUNT('PBI Starter'[Deposit Date]), ALL('dim_DateReference'[Day Number]), 'PBI Starter'[Deposit Date] <= MAX('dim_DateReference'[Date]) + 1, 'PBI Starter'[Start Date] <= EOMONTH(MAX('dim_DateReference'[Date]),0)) ,CALCULATE(COUNT('PBI Starter'[Deposit Date]), ALL('dim_DateReference'[Day Number]), 'PBI Starter'[Deposit Date] <= MAX('dim_DateReference'[Date]), 'PBI Starter'[Start Date] <= EOMONTH(MAX('dim_DateReference'[Date]),0)) )) I need to calculate, for each day number of the month, on average how many starters are 'known about'. I.E on day one of the month, we have 20 known starters for the month, on average we should know about 24 so we need to improve to meet monthly targets. I also have to plot the average by day against the current month actual. Thanks in advance.Calculate multiple time averages in one measure (YD, 7D, 28D, 84D, 7DLY)
Hello all, I'm hoping someone can help me with this query. I'm trying to create a single measure with multiple date calculations and an output. The aim is to compare date periods for orders including Yesterday, Last 7 Days, Last 4 Weeks, Last 8 Weeks, Last 12 Weeks, Last 7 Days (LY). I have a customer order table called orderCidOidShopify which houses order level data (dates, order_id, customer_id...). I also have supporting tables that groups those orders into filterable dimensions like 445 cal, gender, country etc. I have been able to do the majority through seperate meausre like the one below: 7 Day Avg (order) = DIVIDE(CALCULATE ( DISTINCTCOUNT( orderCidOidShopify[order_id] ), FILTER ( 'orderCidOidShopify', orderCidOidShopify[day] < max(orderCidOidShopify[day]) && orderCidOidShopify[day] >= max(orderCidOidShopify[day])-7 ) ),7) Which has outputted the following example: However, if I want to do this for orders, revenue, traffic etc. I will end up with so many individual measures which isn't even considering if I wish to group into weekly or monthly period comparisons. I'm hoping there is a way to create a single measure covering the date comps and also calculates the delta %. i would then have a meausre for each metric instead of a measure for each date comp and metric. The aim is to output like the following: I would then have a sheet which has these tables in multiple metrics and even into small multiples. The main thing I am really struggling with is being able to do the calculation in a single measure. Any help is appreciated.
