Cycle to Date Calculation - difficulties on DAX Measure

[PdJogo]

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 🙂 

[PdJogo]

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! 😀

[amitchandak]

@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-o...

SCD
Guyinacube - https://www.youtube.com/watch?v=tKeaQpWynzg

https://www.youtube.com/watch?v=E1ZABKBpkdg

https://www.zartis.com/scd-implementation-with-temporal-tables-in-power-bi/

[PdJogo · Accepted solution]

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 🙂