Forum Discussion
Prior Half Year Volume
I need to have a report filtered by year and half year that shows the current half year volume, the current half dollar amount, and the volumes for 2 prior half years.
I have done something similar with quarters using dateadd to go back quarters, but dateadd does not have a half year option.
I tried adding a new hierarchy to the date table but I could not get that to work.
Here is a sample pbix
How about
= DATEADD(Date[Date],-6,month)
5 Replies
How about
= DATEADD(Date[Date],-6,month)I tried this
Prior Half Cases = CALCULATE(sum(Data[Cases]),DATEADD('Date'[Date],-6,MONTH))And this is the error I gotOh that error normally is when you dont have a date table. Can you confirm if you are using a date table? if not then try that and use the date from date table in your measure.
If not then this might be an alternate solution:
https://p3adaptive.com/2014/01/defanging-the-contiguous-date-selections-error/
I realized on the sample file that all of the dates in my data table were unique, so there was a 1 to 1 relationship between the tables. I changed it to 1 to many and it worked for the sample file, but it does not work for the actual data.
Here is a table with the correct data by half
Here is the result using the dateadd measure with the filters to 2022 half 1. Note that the column for the previous half gives me the same number as the current half year and the columns for the half year - 2 and half year - 3 match, but the numbers don't match the matrix
Here is the result when I filter for 2022 half 2. It gives me an incorrect number for Half -1 which is repeated in Half - 2 , and then an incorrect number for Half - 3
Each line in the second and third visuals represents a doctor. The lines by doctor in the third screen shot leaves out some lines for the 2022 half 1 that are in second screen shot.
Unfortunately I can't share the actual data.
Does anyone have any ideas why it is behaving like this?
I had a filter on the visual that limited the visual only to doctors who had a certain type of case. It filtered the based on who had those specific type of case in the current period, not taking into account doctors who had those type of cases in the previous periods. I took out the filter and it works fine.
