Forum Discussion

MaloPBI's avatar
MaloPBI
Regular Visitor
6 years ago
Solved

Counter days by action

Hey everybody!

 

I'm trying to solve this problems from two days ago and i cannot find the best solution.

 

I have 2 tables:


Dates and Actions

 
 

  

 

I don't have a relationship between these two tables

 

T

I need to count how many days we have between StartDate and EndDate

I tried to use this Measure: 

CounterDays =
SUMX(Actions;
IF(
MIN(Dates[Dates])>=Actions[StartDate]
&& MAX(Dates[Dates])<=Actions[EndDate];
1;
BLANK()
))


It works for Dates but not for other kind of evaluation.
 
Do you know what i'm doing wrong? 

 
Thank a lot.
 
Malo.
Need Help
  • MaloPBI's avatar
    MaloPBI
    6 years ago

    Hi everyone,

     

    I didn't find any possible solution with measures 😞 ...  but i found a workaroud using calculated tables.

     

    ActionsbyDate = 
SELECTCOLUMNS( 
    FILTER(
            CROSSJOIN(Dates;Actions);
            Dates[Dates]>=Actions[StartDate]
            && Dates[Dates]<=Actions[EndDate]
    );
    "Dates";Dates[Dates];
    "NumMov";Actions[NumMov]
)

     

    Then i can build this model

     

    And now i can get the correct analysis:

    DayValuesAction = COUNT(ActionsbyDate[Dates])

     

     

    If anyone knows how i can get that with measures i'd be great!

     

    Thanks a lot.

     

    Malo.

6 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    Hi MaloPBI,

     

    I don't understand what you want to achieve. Do you want to show how many days there between start and end date for each date in the date-table, so that the days are counted only if the date is between the start and end date?

    Or do you want to calculate the number of days for each Mov?

    Or number of days between start and end when you add filter to the date table?

    Or the number of Movs pr date, based on start and end date?

     

    Try creating a mockup in excel to make it clearer.Cheers,
    Sturla

     

    • MaloPBI's avatar
      MaloPBI
      Regular Visitor
      6 years ago

      Hi sturlaws ,

       

      I try to do it clear.

       

      I need to calculate how many days we have actived as total and do agregation with them.

       

      I leave you an example:

       

       

      There are no problem until NumMov=7 becose it has a Mov in 3 different months where it starts in 19th of january and ends in 30th of march.

       

      It has 70 days but by month it would be 13 for january (from 19 to 31), 28 for february (from 1 to 28) and 30 for march (from 1 to 30).

       

      I hope it could help.

       

      Thanks a lot.

       

      Malo.

       

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi MaloPBI ,

    Please create relationships between the two tables.

    And then, create measures like so:

    Measure = 
IF (
    MAX ( Dates[Dates] ) >= MAX ( Actions[StartDate] )
        && MAX ( Dates[Dates] ) <= MAX ( Actions[EndDate] ),
    1,
    BLANK ()
)
    Measure 2 = SUMX(Actions,[Measure])

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • MaloPBI's avatar
      MaloPBI
      Regular Visitor
      6 years ago

      Hi sturlaws,

       

      I tried your solution like this:

       

       

      And i did the measures:


       

      DayValue = 
CALCULATE(
    IF(
        MAX(Dates[Dates])>=MAX(Actions[StartDate])
        && MAX(Dates[Dates])<=MAX(Actions[EndDate]);
        1;
        BLANK()
    );
    USERELATIONSHIP(
                    Actions[StartDate];
                    Dates[Dates]
    )
)

       

       

      SumOfDayValue = 
CALCULATE(
            SUMX(
                Actions;
                [DayValue]
            );
            USERELATIONSHIP(
                Actions[StartDate];
                Dates[Dates]
            )
)

       

      It gives me the same result as yours but i would need all days as a totalization (132 days)

      If i use a slicer for month, i need to see how many days we have active in Movs every month:

       

      Thanks a lot

      • MaloPBI's avatar
        MaloPBI
        Regular Visitor
        6 years ago

        Hi everyone,

         

        I didn't find any possible solution with measures 😞 ...  but i found a workaroud using calculated tables.

         

        ActionsbyDate = 
SELECTCOLUMNS( 
    FILTER(
            CROSSJOIN(Dates;Actions);
            Dates[Dates]>=Actions[StartDate]
            && Dates[Dates]<=Actions[EndDate]
    );
    "Dates";Dates[Dates];
    "NumMov";Actions[NumMov]
)

         

        Then i can build this model

         

        And now i can get the correct analysis:

        DayValuesAction = COUNT(ActionsbyDate[Dates])

         

         

        If anyone knows how i can get that with measures i'd be great!

         

        Thanks a lot.

         

        Malo.