Forum Discussion

Taznalem's avatar
Taznalem
Frequent Visitor
7 years ago
Solved

How to create calculate spells ?

Hi everybody!   I am developing a PBI to have a tracking of trainings. I would like have a Card where show the maximum number of days of training non-stop. If we suppose one month   1. A Calendar...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Taznalem,

     

    Please check out the demo in the attachment. The main steps are as follows.

    1. Create an index that can identify the continuous days. 

    How_to_create_calculate_spells

    2. Create a measure. 

    Measure =
MAXX (
    SUMMARIZE (
        'Table1',
        'Table1'[Index],
        "non-stop", COUNT ( 'Table1'[Training] )
    ),
    [non-stop]
)

    How_to_create_calculate_spells2

    Best Regards,
    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Taznalem,

 

Please try to change the "Added custom" step and the measure.

 

if [Index] = 0 then 1 else if #"Changed Type"{[Index] - 1}[Date] + #duration(1, 0, 0, 0) = [Date] or
 #"Changed Type"{[Index] - 1}[Date] = [Date] then 1 else 0
Measure =
MAXX (
    SUMMARIZE (
        'Table1',
        'Table1'[Index],
        "non-stop", DISTINCTCOUNT ( 'Table1'[Date] )
    ),
    [non-stop]
)

 

Best Regards,
Dale

Taznalem's avatar
Taznalem
Frequent Visitor
7 years ago

Hi v-jiascu-msft

 

I appreciate you commentary, but that is not a suitable solution, that is because:

 

1. When I create the Added custom with the formula: 

 

if [Index] = 0 then 
1 
else if #"Changed Type"{[Index] - 1}[Date] + #duration(1, 0, 0, 0) = [Date] or
 #"Changed Type"{[Index] - 1}[Date] = [Date] then 
1 
else 
0

 

This create a column where is "1" when the date is the same date that the previous one or when it is a continous day:

 

Date               Custom

19/09/20181
19/09/20181
19/09/20181
19/09/20181
19/09/20181
19/09/20181
20/09/20181
20/09/20181
20/09/20181
25/09/20180
25/09/20181
25/09/20181
25/09/20181
25/09/20181
26/09/20181
26/09/20181
26/09/20181
27/09/20181
27/09/20181
27/09/20181
28/09/20181
28/09/20181
28/09/20181
01/10/20180
01/10/20181
01/10/20181
03/10/20180
03/10/20181
03/10/20181
04/10/20181
04/10/20181
06/10/20180
08/10/20180
08/10/20181
09/10/20181
09/10/20181
13/10/20180
13/10/20181
20/10/20180
20/10/20181
20/10/20181
21/10/20181
21/10/20181
22/10/20181
22/10/20181

 

2. Therefore, when I create the measure is always "1"

 

Thanks in advance

Alvaro

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Alvaro,

     

    Did you check the demo in the attachment? Your input here is just one step of the solution. 

     

     

    Best Regards,
    Dale

    • Taznalem's avatar
      Taznalem
      Frequent Visitor
      7 years ago

      Hi v-jiascu-msft

       

      I am sorry, you are right.  I have forgotten send you the other column with the formula (I've renamed this column as "FinalIndex")

       

      List.Accumulate(let currentIndex = [Index] in Table.SelectRows(#"Added Custom", each [Index] <= currentIndex)[Custom], 1, (state, val) =>
if val = 1 then state else state + 1)

      Fecha                                                            FinalIndex

      19/09/20181
      19/09/20181
      19/09/20181
      19/09/20181
      19/09/20181
      19/09/20181
      20/09/20182
      20/09/20182
      20/09/20182
      25/09/20183
      25/09/20183
      25/09/20183
      25/09/20183
      25/09/20183
      26/09/20184
      26/09/20184
      26/09/20184
      27/09/20185
      27/09/20185
      27/09/20185
      28/09/20186
      28/09/20186
      28/09/20186
      01/10/20187
      01/10/20187
      01/10/20187
      03/10/20188
      03/10/20188
      03/10/20188
      04/10/20189
      04/10/20189
      06/10/201810
      08/10/201811
      08/10/201811
      09/10/201812
      09/10/201812
      13/10/201813
      13/10/201813
      20/10/201814
      20/10/201814
      20/10/201814
      21/10/201815
      21/10/201815
      22/10/201816
      22/10/201816

       

      I think that there is any kind of mistake in the measure because the number is still "1".

       

      Thank you in advance

      Alvaro

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi Alvaro,

         

        Which measure did you use? Since I have uploaded a demo, can you point out the issue in the demo? You already have a column [FinalIndex]. No issues are on my side. So I can't find out what the issue is on your side. Can you share your file?

         

         

        Best Regards,
        Dale