Forum Discussion
Cycle to Date Calculation - difficulties on DAX Measure
Dear all,
I have been strugling with the production of a Cycle to Date (CTD) measure.
Context
I am working on a data model that is basically CRM data: a FactTable with interactions between customers and Sales Reps. This Sales Reps are organized by some territories and each Territory might have different Cycles (lets say I have 4 cycles per year in USA but 12 cycles per Year in India, for example). For this reason, the fact table contains the date of the interaction, the sales rep info and also the cycle key.
Difficulties being faced
I want to create a Field Days measure. This is somehow easy as it is a DistinctCountNoBlank of the DateKey I have in the FactTable. Although, my difficulty is when I want to see this Cycle to Date. Example:
- Cycle Table
Cycle StartDate EndDate Territory
C1 2021 1 Jan 2021 30 Apr 2021 USA
C2 2021 1 May 2021 31 Aug 2021 USA
C1 2022 1 Jan 2022 31 Jan 2022 India
C2 2022 1 Feb 2022 28 Feb 2022 India
This table is connected to the fact table via a CycleKey. I want to be able to do a distinct count of the field days per cycle. Meaning that the following result should be obtained for USA:
Monthly Field Days CTD Field Days
Jan 2022 21 21
Feb 2022 15 36
Mar 2022 15 51
Apr 2022 15 66
May 2022 15 15
Jun 2022 15 30
Jul 2022 15 45
Aug 2022 15 60
How can I do this?
Thanks in advance 🙂
Hello Amit,
Thanks for your answer!
After checkig your answer and searching a little bit more, I find out this one: (17) Implementing running total from arbitrary dates in DAX - Unplugged #34 - YouTube
Guess this is more or less what I was looking for!
Thanks for your help 🙂
3 Replies
- amitchandak
Super User
PdJogo , Check one of two should help
Between Dates - Dates between
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785SCD
Guyinacube - https://www.youtube.com/watch?v=tKeaQpWynzghttps://www.youtube.com/watch?v=E1ZABKBpkdg
https://www.zartis.com/scd-implementation-with-temporal-tables-in-power-bi/
Hello Amit,
Thanks for your answer!
After checkig your answer and searching a little bit more, I find out this one: (17) Implementing running total from arbitrary dates in DAX - Unplugged #34 - YouTube
Guess this is more or less what I was looking for!
Thanks for your help 🙂
Hello again,
Following the implementation of the solution in the video I mentioned, I am facing one other issue 😥
The measures I have only show values if I have date as a field into the visual.For example:
- If I simply run the measure, without any time intelligence applied
- If I run the same measure but applying the time intelligence as follows (similar to the video), no value shows up:
VAR RefDate = MAX ( DimDate[Date] ) VAR LastReset = [Aux Last Reset] VAR DatesToUse = DATESBETWEEN ( DimDate[Date], LastReset, RefDate ) VAR Result = CALCULATE ( SELECTEDMEASURE (), DatesToUse ) RETURN Result- Although, if I put there, for example, Month Year, it will show up the values:
Anyway I can, for example, put the behaviour into the time intelligence to "show always the latest date of the context when there is no date"?
Any insight you might have amitchandak ?
Thanks in advance for your help! 😀
