topic Re: how to calculate percentages between two months (increase rate) in DAX Commands and Tips

how to calculate percentages between two months (increase rate)

TimSchaeffer — Wed, 19 Jun 2024 16:32:58 GMT

Hello,

i am having trouble with calculating the increase rate between two months. i have counted tickets for each year/month(yyyy:mm). this is how i calculate the amount of tickets for each month:

Aantal Verzoeken per Maand =

CALCULATE(

COUNT('tweedelijns melding'[Index]),

ALLEXCEPT('tweedelijns melding', 'tweedelijns melding'[MaandJaar])

)

i want to make a table that shows the total amount for each month (that is currently working), but also the amount of tickets of the previous month, and what the increase/decrease rate is. I tried the following formula, but this doesn't work:

_______________________________________________________________________

Vorige Maand Verzoeken =

CALCULATE(

[Aantal Verzoeken per Maand],

DATEADD('tweedelijns melding'[Aanmelddatum], -1, MONTH)

and i tried:

Vorige Maand Verzoeken =
CALCULATE(
[Aantal Verzoeken per Maand],
PREVIOUSMONTH('tweedelijns melding'[Aanmelddatum])
)

_______________________________________________________________________

)

these formulas show the same amount of tickets as the current month

Can someone tell me how i can calculate this correctly? and just to be sure, is this calculation for the increase/decrease percentages correct?

Percentage Verandering =

DIVIDE(

[Aantal Verzoeken per Maand] - [Vorige Maand Verzoeken],

[Vorige Maand Verzoeken],

)

thanks in advance!

Kind regards, Tim Schaeffer

Re: how to calculate percentages between two months (increase rate)

DataInsights — Wed, 19 Jun 2024 21:02:34 GMT

TimSchaeffer,

Does your model have a date table? If not, it's recommended to create a date table using either Power Query or DAX. Once you create a date table, mark it as a date table, create a relationship to the main table, and use the date table in time intelligence functions such as PREVIOUSMONTH. Also use date table fields in your visual. Example measures:

Aantal Verzoeken per Maand = COUNT ( 'tweedelijns melding'[Index] )

Vorige Maand Verzoeken = CALCULATE ( [Aantal Verzoeken per Maand], PREVIOUSMONTH ( 'Date'[Date] ) )

Your percentage measure looks correct.

Re: how to calculate percentages between two months (increase rate)

TimSchaeffer — Thu, 20 Jun 2024 07:26:55 GMT

Thank you for your answer! i created the data table in the model and it looks like this:

The relation is for ''aanmelddatum'' to the date table, with many to 1.

i tried to use the count function in ''tweedelijns melding'' but this just counts the total and does not filter the data for each month.

Aantal Verzoeken per Maand V2 =

COUNT('tweedelijns melding'[Index])

what should i do to filter it for each year&month?

Re: how to calculate percentages between two months (increase rate)

TimSchaeffer — Thu, 20 Jun 2024 10:27:04 GMT

i checked the data, i see the data is not correctly converted aswell

also i got the table wrong, the count does work but just not the data model / count previous month. i added these columns, and it shows the amount for each month, but not previous or percentages.

Re: how to calculate percentages between two months (increase rate)

DataInsights — Thu, 20 Jun 2024 12:54:29 GMT

TimSchaeffer,

It appears that the date field in the main table is DateTime. This needs to be a Date (no time portion) so it will join properly to the date table. If the main table doesn't have a date field, you can add a column that removes the time portion (Power Query or DAX).

The visual needs to use the Year-Month field in the date table, not the main (fact) table.

It's recommended to use unidirectional relationships, not bidirectional. Note the two arrows on the relationship between the tables. There should be one arrow pointing from the date table to the main table.