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