Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

PREVIOUSMONTH within divide calculation

Hi,

 

I am struggling to find the correct calculation for previousmonth when using the divide function as well.

 

Generic calculation is:

 

Previous =
DIVIDE(
CALCULATE(DISTINCTCOUNT('KPI 42&43'[ULD No.]),'KPI 42&43'[On time/not on time]="On Time"),(
DISTINCTCOUNT('KPI 42&43'[ULD No.])
))
 
Tried several ways to add the previousmonth expression but no result.
 
Kind Regards,
 
Sofiën
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , with help from date table measure like

    calculate ( DIVIDE(
    CALCULATE(DISTINCTCOUNT('KPI 42&43'[ULD No.]),'KPI 42&43'[On time/not on time]="On Time"),(
    DISTINCTCOUNT('KPI 42&43'[ULD No.])
    )), previousmonth('Date'[Date]) )

     

    or


    calculate ( DIVIDE(
    CALCULATE(DISTINCTCOUNT('KPI 42&43'[ULD No.]),'KPI 42&43'[On time/not on time]="On Time"),(
    DISTINCTCOUNT('KPI 42&43'[ULD No.])
    )), dateadd('Date'[Date]),-1,month )

  • DataInsights's avatar
    DataInsights
    4 years ago

    Anonymous,

     

    There are various ways to achieve this. You'll need a date table for these measures, with a relationship to the fact table.

     

    Approach 1:

     

    Previous =
CALCULATE (
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( 'KPI 42&43'[ULD No.] ),
            'KPI 42&43'[On time/not on time] = "On Time"
        ),
        ( DISTINCTCOUNT ( 'KPI 42&43'[ULD No.] ) )
    ),
    PREVIOUSMONTH ( DimDate[Date] )
)

     

    Approach 2:

     

    Previous =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'KPI 42&43'[ULD No.] ),
        'KPI 42&43'[On time/not on time] = "On Time",
        PREVIOUSMONTH ( DimDate[Date] )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'KPI 42&43'[ULD No.] ),
        PREVIOUSMONTH ( DimDate[Date] )
    )
)

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , with help from date table measure like

    calculate ( DIVIDE(
    CALCULATE(DISTINCTCOUNT('KPI 42&43'[ULD No.]),'KPI 42&43'[On time/not on time]="On Time"),(
    DISTINCTCOUNT('KPI 42&43'[ULD No.])
    )), previousmonth('Date'[Date]) )

     

    or


    calculate ( DIVIDE(
    CALCULATE(DISTINCTCOUNT('KPI 42&43'[ULD No.]),'KPI 42&43'[On time/not on time]="On Time"),(
    DISTINCTCOUNT('KPI 42&43'[ULD No.])
    )), dateadd('Date'[Date]),-1,month )

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    Anonymous,

     

    There are various ways to achieve this. You'll need a date table for these measures, with a relationship to the fact table.

     

    Approach 1:

     

    Previous =
CALCULATE (
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( 'KPI 42&43'[ULD No.] ),
            'KPI 42&43'[On time/not on time] = "On Time"
        ),
        ( DISTINCTCOUNT ( 'KPI 42&43'[ULD No.] ) )
    ),
    PREVIOUSMONTH ( DimDate[Date] )
)

     

    Approach 2:

     

    Previous =
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'KPI 42&43'[ULD No.] ),
        'KPI 42&43'[On time/not on time] = "On Time",
        PREVIOUSMONTH ( DimDate[Date] )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'KPI 42&43'[ULD No.] ),
        PREVIOUSMONTH ( DimDate[Date] )
    )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for the help, it works. Is there also something as previous week to add for this. Then for example if it is wednesday today. Previous week should NOT calculate from last week Thursday till this week Wednesday, but it should calculate previous week Monday till Sunday.

      • DataInsights's avatar
        DataInsights
        Icon for Super User rankSuper User
        4 years ago

        Anonymous,

         

        Glad that works. You can use an IF statement to check if the current day is Wednesday, and then include the appropriate days.