12 Months Rolling Measure total incorrect in table
Hi everyone, I am currently facing an issue in DAX Power BI. I am trying to display a 12 Months Rolling Measure for customers. However, when displayed in a table with only 1 customer column, I have an issue with the totals displayed. I have been trying all kinds of solutions found online and suggestions from colleagues, but I can't seem to find the solution. I might be overthinking the problem and I'm sure an easy fix exists. Enough of me rambling, here is the DAX formula used for the 12 Months Rolling Sales measure : 12M Rolling Sales= Calculate( SUM([Sales Amount]; DATESBETWEEN('Dates'[Date]; NEXTDAY(SAMEPERIODLASTYEAR( LASTDATE('Dates'[Date]))); LASTDATE('Dates'[Date]))) I've tried using SUMX, Hasonefilter and Hasonevalue and even rebuilding the formula so that it's less complex, but everytime I have the same wrong total. Here's a very simplified example of the issue I'm facing : the total row is not correct Could anyone help me out ? Thank you very much, I'm really under lot of pressure to get this fixed. Thank you! Best regards Dino.
Calculate be 12 months or a rollout by month
On the following table "QuoteConvertionStartDate" I have columns for Date, Total, and Accepted I need to calculate the "Total 12 moth roll" and "Accepted 12 Moth Roll". I did the numbers manually to give a result. For example to calculate the Total 12 months roll for Tusday January 1 2019 I have to sum: Tusday January 1 2019 to Thrusday February 1 2018 = 40 To calculate the Total 12 months roll for Saturday December 1 2018 I have to sum: Saturday December 1 2018 to Monday January 1, 2018 = 41 To calculate the Total 12 months roll for Thusday November 1 2018 I have to sum: Thusday November 1 2018 to Friday December 1, 2017 = 45 and so on. I have to do the same for Accepted. I tried the following DAX but is giving me the total by month and is giving me the total by 12 months or rolling back 12 months: __Value L12M = VAR __EndDate = EOMONTH(LASTDATE(QuoteConvertionStartDate[Date].[Date]),0) VAR __StartDate = DATE(YEAR(__EndDate),MONTH(__EndDate) - 12, 2) RETURN CALCULATE(QuoteConvertionStartDate[Total], DATESBETWEEN(QuoteConvertionStartDate[Date].[Date], __StartDate, __EndDate)) Can someone help please. Thanks
Rolling 12 and All() together acting in unexpected way (V2)
I have a very simple table, that I'm trying to do the following: For each given month, find the sum of the last 12 month for each person plus sum up all(persons) who that particular month have a Flag = Yes. Put another way: the rolling 12 of all people who have a flag = "yes" that month. I failed with this formula Rolling for all = CALCULATE ( [Sum Value], ALL ( 'Table'[Person] ), DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -12, MONTH ), 'Table'[Flag] = "Yes" ) Here is the behavior I want: I have a value of 1,000 for each month for each person in this test. So, for each individual, the rolling 12 should be 12,000. If in a given month, 5 people have a flag of 'yes', then I should have a value of 60,000 total - regardless of what flags they had in the last 12 months. What actually happens: The formula looks at the past 12 months and only includes a given month IF the flag = "yes". So, someone with that flag for only 3 of the last 12 months would only contribute 3,000 to total. I can see why it does that, but I don't want that. I want the full 12 months rolling if the current month flag = "yes", irrespective of what the last 12 months had for a flag. Put another way: if there are 4 people with the flag = yes then it should show 48,000. Then if the very next month 1 more person gets the flag = yes (5 total), then that same month it should jump to 60,000. Instead, it jumps to 49,000. (For this test, I have 1,000 per person per month going back in time) Person Date Value Flag C October 2019 1000 Yes E October 2019 1000 Yes D October 2019 1000 Yes B October 2019 1000 Yes A October 2019 1000 Yes E September 2019 1000 Yes D September 2019 1000 Yes C September 2019 1000 Yes B September 2019 1000 Yes A September 2019 1000 Yes C August 2019 1000 Yes E August 2019 1000 Yes D August 2019 1000 Yes B August 2019 1000 Yes A August 2019 1000 Yes E July 2019 1000 Yes D July 2019 1000 Yes C July 2019 1000 Yes B July 2019 1000 Yes A July 2019 1000 Yes E June 2019 1000 D June 2019 1000 Yes C June 2019 1000 Yes B June 2019 1000 Yes A June 2019 1000 Yes C May 2019 1000 Yes E May 2019 1000 D May 2019 1000 Yes B May 2019 1000 Yes A May 2019 1000 Yes D April 2019 1000 Yes C April 2019 1000 Yes E April 2019 1000 B April 2019 1000 Yes A April 2019 1000 Yes E March 2019 1000 D March 2019 1000 Yes C March 2019 1000 Yes B March 2019 1000 Yes A March 2019 1000 Yes C February 2019 1000 Yes E February 2019 1000 D February 2019 1000 Yes B February 2019 1000 Yes A February 2019 1000 Yes E January 2019 1000 D January 2019 1000 Yes C January 2019 1000 Yes B January 2019 1000 Yes A January 2019 1000 Yes E December 2018 1000 D December 2018 1000 Yes C December 2018 1000 Yes B December 2018 1000 Yes A December 2018 1000 Yes E November 2018 1000 D November 2018 1000 Yes C November 2018 1000 Yes B November 2018 1000 Yes A November 2018 1000 Yes D October 2018 1000 Yes C October 2018 1000 Yes E October 2018 1000 B October 2018 1000 Yes A October 2018 1000 Yes E September 2018 1000 D September 2018 1000 Yes C September 2018 1000 Yes B September 2018 1000 Yes A September 2018 1000 Yes C August 2018 1000 Yes E August 2018 1000 D August 2018 1000 Yes B August 2018 1000 Yes A August 2018 1000 Yes E July 2018 1000 D July 2018 1000 Yes C July 2018 1000 Yes B July 2018 1000 Yes A July 2018 1000 Yes E June 2018 1000 D June 2018 1000 Yes C June 2018 1000 Yes B June 2018 1000 Yes A June 2018 1000 Yes E May 2018 1000 D May 2018 1000 Yes C May 2018 1000 Yes B May 2018 1000 Yes A May 2018 1000 Yes E April 2018 1000 D April 2018 1000 Yes C April 2018 1000 Yes B April 2018 1000 Yes A April 2018 1000 Yes E March 2018 1000 D March 2018 1000 Yes C March 2018 1000 Yes B March 2018 1000 Yes A March 2018 1000 Yes E February 2018 1000 D February 2018 1000 Yes C February 2018 1000 Yes B February 2018 1000 Yes A February 2018 1000 Yes E January 2018 1000 D January 2018 1000 Yes C January 2018 1000 Yes B January 2018 1000 Yes A January 2018 1000 Yes https://1drv.ms/u/s!AlCaI3WpECWQgbsXLUxAXeUrQpSAcQ?e=nXMrDKDebug help please
I have simplified my table to one value (Charge90) per month. I have a measure that shows me the rolling 12 total. Rolling individual = sumx(DATESINPERIOD(PostMonthly90[Month_Post],LASTDATE(PostMonthly90[Month_Post]),-12,MONTH),[Sum Charge90]) Then I want a measure that shows the sum of those rolling 12's for everyone who has the status of "Partner" in the Partner_Status. To start that, I tried to make a measure that just showed the rolling 12 if someone was a partner with this.... Seemed straight forward... only partners rolling = sumx(filter(PostMonthly90,PostMonthly90[Partner_Status]="Partner"),[Rolling individual]) THis does show a blank if someone is not a partner that month. however, if they are a partner, it doesn't show the measure "Rolling Individual", it just shows the sum of the current months Charge90, ie, NOT the rolling 12. Somehow the rolling 12 gets removed? weird... The measure for "only partner rolling" contains "rolling individual" but actually shows Charge90?!?
