Forum Discussion
Prior Period Comparative Dynamic Date Range
Lets try this way
Prior Year Revenue = CALCULATE(sum(Revenue[total_net_lc(exvat)]),DATEADD(Dates[Date],-1,YEAR))
Can you send a data example?
Dear Rafael - thanks for a reponse. Apologies I should have addedd sample data to the original post.
There are two tables - ''Dates'', and ''Revenue'' and the only relationship is the link between invoiced_date on the revenue table, and Date on the DATES table. An example of the content of each can be seen in the first screen grab.
When I toggle on a certain date range I would like the ''PRIOR YEAR REVENUE'' column to populate with the priod year date.
The second screenshot shows a date filter of 1/09/2017 to 30/09/2018 and the revenue for this period is 5,355,336, and I'm struggling to write a DAX formula to populate the PRIOR YEAR REVENUE column (which would be for he period 01/09/16 to 30/09/17). Screen grab 3 shows that the revenue for this period is 4,851,931 and this is the figure I would like to populate.
Currently the DAX formula being used is Prior Year Revenue = CALCULATE(sum(Revenue[total_net_lc(exvat)]),FILTER(Dates,Dates[Date] <= MAX(Revenue[invoiced_date])-364)) which is not giving the desired response.
Any suggestions?
Lets try this way
Prior Year Revenue = CALCULATE(sum(Revenue[total_net_lc(exvat)]),DATEADD(Dates[Date],-1,YEAR))Thanks Rafael, I seemed to be going round in circles. Appreciate your help - TY!
