User Profile
Tani4ka
Helper II
Joined 5 years ago
User Widgets
Contributions
Preceeding 12 quarters from selected filter Q Year
Hello, Would be grateful for some help resolving my case. I need to create a measure that will allow users to see 9 quarters prior and including the one they select in the filter (10 quarters on the chart) So far my measure works partially: the quarter users select shows only the 1st month values of the selected but other quarters are correctly displayed. I can't figure out why and how to make the selected quarter also showing the full 3 months values instead of one. On the below you can see I selected Q4 25 and the value in the bar chart shoudl be 9.6M but it returns only 3.5 that matches the first month of Q4 25. My measure is next: Visits Last 13 Quarters = VAR AnchorDate = COALESCE ( SELECTEDVALUE ( 'dim_time'[Start of Quarter] ), MAX (visits[date] ) ) VAR AxisQ = SELECTEDVALUE ( 'Presentation Calendar'[Start of Quarter] ) RETURN CALCULATE ( [Value], REMOVEFILTERS ( 'dim_time' ), KEEPFILTERS ( DATESINPERIOD ( 'dim_time'[Date], AnchorDate, -9, QUARTER ) ), TREATAS ( { AxisQ }, 'dim_time'[Start of Quarter] ) ) I tried to change the measure to use Quarter Year insted but it's not in the date format hence the measure doesn't work. Visits - table with the date and the value dim_time - calendar that used for filters Presentation Calendar - calendar used for measure . (Presentation Calendar = dim_site) Many thanks!Re: Previous Period measure
I realised were the problem was - i used incorred date columns. The measure now works well but when i add a custom date columns to sort the data correctly i get 0 resutls. It only react to the main date column and the order is not correct. I tried updating the date columns in the measure to the cutom created but no luck. Any suggestions?Re: Previous Period measure
thank you for your responce but I need a bit more clarity on how to use it. I've update the measure but is gives rather 0 results PM_created_NGT = SWITCH ( TRUE (), ISINSCOPE ( 'dim_time'[Month] ), [JA Created PM], ISINSCOPE ( 'dim_time'[Quarter]), [JA Created PQ], ISINSCOPE ( 'dim_time'[Year] ), [JA Created PY] ) JA Created PY = VAR createdActual = CALCULATE( SUM(Raw_data[JA created]) ) VAR createdLY = CALCULATE( SUM(Raw_data[JA created]), PREVIOUSYEAR(dim_time[date]) ) RETURN IF( ISBLANK(createdLY) || ISBLANK(createdActual) || createdLY = 0 || createdActual = 0, BLANK(), DIVIDE(createdActual - createdLY, createdLY)) JA Created PY returns correct resutls for the total YoY but the rest of the lines are wrong. Can you kindly point me to what I am doing wrong? Many thanksPrevious Period measure
hello, I need to create a measure that will show comparison for a previous period when the data is monthly/quarterly of Yearly. So far i created one that gives correct compariosn MoM but when 2 or more are selected the resutls are completely off as I used PREVIOUSMONTH. is there any function that can do PREVIOUSPERIOD? My measure: JA Sent PM = VAR sentActual = CALCULATE( SUM(Raw_data[JA sent]) ) VAR sentLY = CALCULATE( SUM(Raw_data[JA sent]), PREVIOUSMONTH(dim_time[Date]) ) RETURN IF( ISBLANK(sentLY) || ISBLANK(sentActual) || sentLY = 0 || sentActual = 0, BLANK(), DIVIDE(sentActual - sentLY, sentLY)) Many thanks
