Summarize last month

[Rallmo]

Hi,

I'm in need for some DAX help.

I'm trying to compare the numer of incidents from the last month to the month prior to that. The closest thing I have come to solve my problem is the DAX below, but as you can se it counts from Today and 30 days backwards and not last month.

1 månad = CALCULATE
(COUNTROWS('KPI03 - Data (INC)');
FILTER
(ALL
('KPI03 - Data (INC)'[Date Created]); 'KPI03 - Data (INC)'[Date Created] >= TODAY()-30)
)

I might be missing something obvious, but any ideas how to change this?

[v-yuta-msft]

@Rallmo ,

You can modify your measure like below and check if it can meet your requirement:

1 månad =
CALCULATE (
COUNTROWS ( 'KPI03 - Data (INC)' );
FILTER (
ALL ( 'KPI03 - Data (INC)'[Date Created] );
MONTH ( 'KPI03 - Data (INC)'[Date Created] )
>= MONTH ( TODAY () ) - 1
)
)

Community Support Team _ Jimmy Tao

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

[Rallmo]

@v-yuta-msft 

The result I get is the total number of requests in the dataset and not only for last month. So it doesn't seem to aknowledge the timefilters at all.

[v-yuta-msft]

@Rallmo ,

Could you share some sample data and clarify more details about your requirement?

Community Support Team _ Jimmy Tao

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

[Rallmo]

@v-yuta-msft  Sorry the mail went to the spam folder so I didn't see your reply untill now. 😐

I have som data but am I not able to attach files here?

[Anonymous]

I don't think we can upload files, but you can insert links to files stored elsewhere.

[Anonymous]

Try this:

Diff = COUNTROWS('KPI03 - Data (INC)')

- CALCULATE(

     COUNTROWS('KPI03 - Data (INC)'),

          PREVIOUSMONTH(DATE(YEAR('KPI03 - Data (INC)'[Date Created]),

          MONTH('KPI03 - Data (INC)'[Date Created]),1)))

[Rallmo]

I get the following errormessage when I try that, I will translate it for you. 🙂

Unable to collect data for the visual.

Calculationerror in the measurment [Diff]. A datecolumn that contains dual dates has been specified in the call to the function PREVIOUSMONTH. This is not supported.

[Anonymous]

I would use a function that is equal to 12*Year + Month of the date column, and also the same for the current day, then you can filter when they are equal, different by one, different by two etc.

[Rallmo]

Thanks for the answer @Anonymous .

Do you mean a calculated column with the information?