Counter days by action

[MaloPBI]

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.

[MaloPBI]

Hi everyone,

I share with you the .pbix and excel file in order to do it easy if you want to test something.

https://drive.google.com/open?id=1j3U8F96kPwYy4H03nGjnhdDZK-OrjdHQ

Thanks a lot.

Malo

[Icey]

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]

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 · Accepted solution]

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]

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]

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.