Masure of The difference between individual months for 12 RM
Hello, I'd like to insert a column with calculated difference between the previous months and 'current' months. The difference should base on the values from 12 RM measure as indicated below. 12 RM Category rate = IF(AND(ISFILTERED('Calendar'[Month Yr]), DATEDIFF(MAX('Calendar'[Dates]), TODAY()-1,MONTH)<>0), SUMX(VALUES('Calendar'[Month Yr]), [Pre 12 RM Cat rate]) , --To force the '0' and the division by 12 for the RollBack [Pre 12 RM Cat rate]) Pre 12 RM Cat rate = VAR RollBack_Table = CALCULATETABLE(FILTER(DATESINPERIOD('Calendar'[Dates], MAX('Calendar'[Dates]), - 12 , MONTH) , 'Calendar'[Dates] = EOMONTH('Calendar'[Dates],0)) , 'Calendar'[Flag_Projec]) VAR RollBack_Table_2 = CALCULATETABLE(FILTER(DATESINPERIOD('Calendar'[Dates], MAX('Calendar'[Dates]), - 11 , MONTH) , 'Calendar'[Dates] = EOMONTH('Calendar'[Dates],0)) , 'Calendar'[Flag_Projec]) VAR Cat_current = CALCULATE([MTD cat rate], 'Calendar'[Current Month]) RETURN IF(AND(ISFILTERED('Calendar'[Month Yr]), DATEDIFF(MAX('Calendar'[Dates]), TODAY()-1,MONTH)<>0), SUMX(RollBack_Table , [MTD cat rate]) , SUMX(RollBack_Table_2,[MTD cat rate]) + Cat_current ) Do you have any ideas how could I cope with that? Thank you in advance! MikFind duplicates between "date selected" and "date selected" - 30 days
Hi! 🙂 I have date filters (slicer) for year, month and day in a model that uses a table with [ID] and [Date] I want to show the duplicates between the "Date selected" and ("Date selected" -30 days) This is the table: If i select "December 1st, 2022" it should only show these: Thank you!Time Intelligence Puzzle
Looking for a DAX formula that has some sort of dynamic PARRALLELPERIOD functionality. I have 2 periods as follows: "Period A" (a baseline if you will) and "Period B". Period A spans 3.5 months. Period B is all dates after Period A ends. When a user selects a date range in Period B for a measure (e.g., Sales) I want the DAX formula to compute the average Sales for all exactly matching periods within Period A. For example, lets say the user randomly picks 11 days is Period B (like Sunday June 5 thru Wednesday June 15). And let's say Sales for that period equal 10,050. And lets say Period A begins on March 1 and ends on May 15. I would want to compare the 10,050 to the average of each matching 11 day period within Period A. A matching period (within Period A) would have to start on Sunday and end on the Wednesday 11 days later. Specifically, based on my logic, the periods in this particular example are as follows: 3/6-3/16, 3/20-3/30, 4/3-4/13, 4/17-4/27, and 5/1-511. Each of those 5 periods have different total Sales. I then want the average of those 5 sums. That average would then be comparable to the 10,050 for 6/5-6/15. Again, this formula would have to dynamically support any range of dates a user might select within Period B whether that be 1 day, 5 days, 10 days, 13 days or whatever. Lastly the model is a simple transactional sales fact table attached to a calendar table.Date Table and Time Intelligence Functions not working anymore
Hello I dont know the reason but i had properly working date table function and time intelligence functions before, but not working anymore. I dont know if its related with latest updates. As you may see in the below screenshot it gives error after Date function at 3rd row. If i dont touch the dax formula its ok but if i F2 + enter all my report will blow up. Same happens to my DATESINPERIOD formula, As you can see below, last parameter is not accepting MONTH as Interval. which was perfectly working before. If i re enter on this formula, again all my report will be broken. Thanks in advance for you help
Expand Dates in Large Data Set - PowerQuery and Dax
I have a large data set in a proper star schema. However, i'm struggling with some reports out of it. I'll explain what i'm currently doing, and hopefully someone can tell me a better way (or more elegant) way to accomplish something similar. Data set: There's many more tables that have proper relationships (Aprox 30 more), but for the purpose of this exercise, that's all we need. Most of the work happens in the ResourceActualDetail table it looks like this (with some columns deleted): ResourceAllocationId AllocationPercentage EndDate StartDate 794 1 4/1/2018 0:00 2/25/2018 0:00 795 1.15 3/4/2018 0:00 2/25/2018 0:00 795 1.0875 3/11/2018 0:00 3/4/2018 0:00 795 1 4/1/2018 0:00 3/11/2018 0:00 796 1.05 3/4/2018 0:00 2/25/2018 0:00 797 0.725 3/4/2018 0:00 2/25/2018 0:00 1531 1 3/11/2018 0:00 2/25/2018 0:00 1721 1 4/1/2018 0:00 2/25/2018 0:00 Desired Output: ResourceAllocationId AllocationPercentage EndDate StartDate Date 794 1 4/1/2018 0:00 2/25/2018 0:00 2/26/2018 0:00 794 1 4/1/2018 0:00 2/25/2018 0:00 3/4/2018 0:00 794 1 4/1/2018 0:00 2/25/2018 0:00 … 794 1 4/1/2018 0:00 2/25/2018 0:00 4/1/2018 0:00 Notice the 7 day increments. And the same for every ResourceAllocationID so that I can graph it like so: (error in Graph dates, should be with 7 day increments). Where there is a different line for each Resource (connected through relationships to ResourceAllocation and ResourceActualDetail). Even if we can get it to a point where we can do that for each ResourceAllocationId I can figure out the rest 🙂 Current Approach: These data tables are quite large already(~1M rows) , and my approach only has bloated that. Trying to do everything as close to the source, this what I came up with: Let Source=PowerBI.Dataflows(null), ... #"Changed Type" = Table.TransformColumnTypes(ResourceActualDetail1,{{"EndDate", type number}, {"StartDate", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each List.Numbers([StartDate],([EndDate]-[StartDate])/7,7)), #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type datetime}, {"EndDate", type datetime}, {"StartDate", type datetime}}) in #"Changed Type1" Essentially, I change the StartDate and EndDate fields to numeric Values and then I calculate the beginning of each week before i switch back all those values to Date type. This part of the query doesn't fold, so I cannot take the advantage of the PowerFlow to do it. It also makes each refresh very slow (should be refresed multiple times a day). Any suggestions or ideas?? Obviously looking for the most efficient way to achieve this so that it won't take many resources (I do have a premium instance). Thanks!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 ) )Adding 2 custom lines to line and stacked column chart
I currently have a pareto chart by number of cases per event date. I'm trying to add a rolling 7 day average calculation to this but I keep getting: Where I was hoping to have something like this? I'm currently running my 7 day rolling average by [Event Date]: 7 Day Rolling Average - Notification Date = CALCULATE(SUM('Sheet1'[Epi Date Count]),DATESINPERIOD('Sheet1'[Event Date],LASTDATE('Sheet1'[Event Date]),7,DAY)) / CALCULATE(DISTINCTCOUNT('Sheet1'[Event Date]),DATESINPERIOD('Sheet1'[Event Date],LASTDATE('Sheet1'[Event Date]),7,DAY)) Is there any way this can be done? Thanks in advance Please find file attached https://www.dropbox.com/s/c3hfci8jabydxv1/Pareto.pbix?dl=0Calculate average of measure values datesbetween
I have two tables 1. Dimdate = a date key 2 'Table' = data table with PERSON_ID They are not related as I have created a measure to count % attendance. If I have calculate the table below: Is there a way to calculate the average of percentages for PERSON_ID = 135 for dates between DATE(2018,1,1),DATE(2018,12,31) e.g. average of (80.49% & 60% = 70.25%) I've tried these but cant seem to get them to work. Measure 4 = AVERAGex(VALUES('Employee Absence Full Table'[Month and Year]),[Measure 3]) or Measure 4 = CALCULATE(AVERAGex('Employee Absence Full Table',[Measure 3]),DATESBETWEEN('DimDate'[Dates],DATE(2018,01,01),DATE(2018,12,31))) File attached: https://www.dropbox.com/s/u5ztxrg4g2g5qpi/Sample3%28updated%29.pbix?dl=0
