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.

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.