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

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.