Average of a Measure for a period of time
I have a measure called "First30Days" that goes back for 30 days of the Daily Sales and summarizes 30 days of sales for each calendar day. (I know the dataset is not every calendar day, that is ok.) First30Days = CALCULATE(('SalesTable'[DailySales]),DATESINPERIOD('Date Dim'[Date],NEXTDAY('Date Dim'[Date])-30,30,DAY)) As the screenshot indicates, this works fine and as expected. What I can't seem to figure out is how to average the First30Days column over 30 calendar days. I should end up having a new measure that shows me the 30 day average of the First30Days. Based on the screenshot, i should get 647,547 in my new column on the last row. (And, I will get a result in each row, but I am only showing for that one day in this example.) Year Quarter Month First 30 Days AVERAGE OF First 30 Days 2020 Qtr 2 June 22 $492,785 2020 Qtr 2 June 23 $515,496 2020 Qtr 2 June 24 $563,194 2020 Qtr 2 June 25 $589,350 2020 Qtr 2 June 26 $581,721 2020 Qtr 2 June 29 $612,746 2020 Qtr 2 June 30 $647,252 2020 Qtr 3 July 1 $660,788 2020 Qtr 3 July 2 $669,226 2020 Qtr 3 July 3 $629,239 2020 Qtr 3 July 6 $649,151 2020 Qtr 3 July 7 $680,475 2020 Qtr 3 July 8 $678,310 2020 Qtr 3 July 9 $679,352 2020 Qtr 3 July 10 $671,994 2020 Qtr 3 July 13 $678,355 2020 Qtr 3 July 14 $718,676 2020 Qtr 3 July 15 $732,290 2020 Qtr 3 July 16 $733,909 2020 Qtr 3 July 17 $704,970 2020 Qtr 3 July 20 $709,198 647,547
divide function returns 0 instead of number
hello i have a measure where im dividing two measures numinator : [active users 30 days ago]-[active users prevoius 30 days ago] denominator :[ active users 30 days ago] both measures im using return values seperately and when subtracting also, but after using the divide function it returns zero the DAX code for active users 30 days ago: CALCULATE(distinctCOUNT(table[user_id]),DATESBETWEEN('Calender'[Date], MAX('Calender'[Date]) -30, MAX('Calender'[Date]))) DAX code for active users previous 30 days ago: CALCULATE(distinctCOUNT(table[user_id]) ,DATESBETWEEN('Calender'[Date], MAX('Calender'[Date]) -60, MAX('Calender'[Date]) -30))) ) DAX for the division calculation= MOM= calculate(divide( [active users 30 days ago]-[active users prevoius 30 days ago],[active users prevoius 30 days ago])) the results im getting:
Calculating quote ages
Hello, I have a data set with month and count of quotes. I need a measure that will calculate Count the number of quotes that are: current (1 - 4 months); 5 - 7 months old; 8 - 12months old and greater than 12 months old based off today's current month. An example of what I want is below. I will then put the measures into a bar chart to see the "age" of the quotes. TIA
Calculating Same Period Last Month !!!
Hi Geniuses, I have created a measure to calculate MTD sales of my product category now I want to calculate sales of same premiod last month to compare. My Dax formula is returning the same result as current month MTD. PREMIUN SPLM = CALCULATE([MTD PREMIUM Sales], DATESBETWEEN('Calendar'[Date],DATE(YEAR( TODAY() ), MONTH( TODAY() )-1, 01), TODAY()-30 ) ) Can you please help me how I can fix this?Active relationship causing count to be wrong
I'm trying to count number of empty propertys by counting the previous tenancy numbers that exist in the empty property table. "hraptvpd" It's worth mentioning that hraptvpd is directly quering the database. hraptvpd has an active relationship of next_tency_st to DateQuery[Date] It also has an inactive relationship of prev_tency_end to DateQuery[Date] The only way I know it was empty (it could be empty multiple times) is the gap in dates between the previous tenancy end (hraptvpd[prev_tency_end]) and next tenancy start (hraptvpd[next_tency_st]) I have tried: Which gives the error: Count of property empty = CALCULATE( USERELATIONSHIP(hraptvpd[prev_tency_end], DateQuery[Date]), COUNT(hraptvpd[prev_tency]), DATESBETWEEN(DateQuery[Date], hraptvpd[prev_tency_end], hraptvpd[next_tency_st]) ) ------------------------------------------------------ Count of property ID = CALCULATE( COUNT(hraptvpd[prev_tency]), FILTER(hraptvpd, hraptvpd[prev_tency_end] < DateQuery[End of Month] && hraptvpd[next_tency_st] >= DateQuery[End of Month]) ) The second solution above can only count the number of tenancies that started on the end of month: If anyone can help I'd be enternally grateful.Calculate result for next 4, next 10 and next 16 weeks based on month from matrix row
Based on the first column of the matrix (year and month hierarchy), i would like to create a measure that perform a calculation based on another measure. The second column "Calculation" is fine as it receives the date input from the date hierarchy. However, i struggle to perform the calculation for "Next 4" weeks (and also for "Next 10", "Next 16", etc.) The calculation "Next 4" is supposed to take the first date of the month, return the following 4 weeks (or 28 days), and evaluate another measure for the set of dates returned. In example, for january 2020, I want it to perform the calculation for the range between January 1st and January 28th. Similarly, the calculation "Next 10" is supposed to take the first date of the month, return the following 10 weeks (or 70 days), and evaluate another measure for the set of dates returned. In example, for january 2020, I want it to perform the calculation for the range between January 1st and March 11th. "Estimated revenue" is calculated in another measure, which works fine for the monthly date ranges provided by the table. Planned next 4 weeks (dynamic) = // Calculating estimated revenue for the next 28 days from the start of a given month. CALCULATE( [Estimated revenue]; DATESINPERIOD( Revenue[Date]; STARTOFMONTH(DateTable[Date]); 28; DAY ) )
