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 with all days of the month

2. A Query with the trainings. These are classified by days and include all the relevant information about the trainings

 

The question if that I don't know how to calculate the maximum number of days that I have been training non-stop along the month.

 

Thanks in advance

  • 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

9 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    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

    • Taznalem's avatar
      Taznalem
      Frequent Visitor
      7 years ago

      Hi!

       

      Your answer has been very usefull, but I have a problem yet. I have the same date in differents rows, for example:

       

      column1         column2       column3

      28/09/2018     data1           data2

      28/09/2018     data3           data4

      28/09/2018     data4           data6

      28/09/2018     data5           data8

      29/09/2018     data9           data10

      29/09/2018     data11         data12

       

      Would you know how to apply yout last message to this?

       

      Thanks!

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft 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 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 with all days of the month

    2. A Query with the trainings. These are classified by days and include all the relevant information about the trainings

     

    The question if that I don't know how to calculate the maximum number of days that I have been training non-stop along the month. Thanks in advance