topic Re: Errors When trying to calculate Month Over Month differences in my Accession; number in DAX Commands and Tips

Errors When trying to calculate Month Over Month differences in my Accession; number

MerryA — Mon, 17 Jun 2024 19:14:44 GMT

New to Power BI - have watched many you tube videos as well as our company's training. I am getting errors when calculating Month-over-Month difference %. The calculation seems to be summing the accession numbers instead of counting them. Also gives me errors due to multiple dates being the same. Listed below is an example of my data. I have tried creating a new measure:

ParallelLastMon = CALCULATE(Sum(Data[Accession]),PARALLELPERIOD('Calendar'[Date],-1,MONTH)) per 1 youtube video and

Exam Count Prev Month = CALCULATE([Exam Count],PARALLELPERIOD(Data[Completeddttm],-1,MONTH)) Both of these give me errors. @ links to members, content

This is what I get:

Sample data:

Accession Number	Completed Date time
123456789	1/3/2024 13:04
234567891	5/2/2024 9:27
345678993	6/3/2024 16:54
456790095	2/22/2024 17:32
567901197	2/13/2024 10:23
679012299	6/3/2024 7:32
790123401	3/25/2024 11:25
901234503	3/3/2024 13:57
1012345605	5/31/2024 13:13

Re: Errors When trying to calculate Month Over Month differences in my Accession; number

Irwan — Tue, 18 Jun 2024 01:39:49 GMT

hello MerryA

please check if this will acommodate your need.

as you have two table which are the sample data table as first table and the table above sample data as second table.

1. create a unique id for table relationship. Since you use month name in first table, then i created a calculated column in first table contain of month name.

2. connect those two table with month name as the column relationship

3. after that, you can do calculation easily to get the value

ParallelLastMonth = CALCULATE(SUM('Table'[Accession Number]))

Count of Ascension = CALCULATE(COUNTROWS('Table'))

Hope this will help you.

Thank you.

Re: Errors When trying to calculate Month Over Month differences in my Accession; number

lbendlin — Tue, 18 Jun 2024 01:53:54 GMT

Note that PARALLELPERIOD has some very specific limitations

Remarks

This function takes the current set of dates in the column specified by dates, shifts the first date and the last date the specified number of intervals, and then returns all contiguous dates between the two shifted dates. If the interval is a partial range of month, quarter, or year then any partial months in the result are also filled out to complete the entire interval.

Most likely not what you want.

Re: Errors When trying to calculate Month Over Month differences in my Accession; number

MerryA — Tue, 18 Jun 2024 12:28:27 GMT

Irwan - Thank you so much for your response. so your table is doing the same thing mine is in the ParallelLastMonth - It is Summing the Accession numbers instead of counting. I have tried replacing the SUM with Count but it still does not give me the comparison to the last month in the last column.

Re: Errors When trying to calculate Month Over Month differences in my Accession; number

MerryA — Tue, 18 Jun 2024 15:43:35 GMT

Thank you for the explaination!

Re: Errors When trying to calculate Month Over Month differences in my Accession; number

Irwan — Thu, 20 Jun 2024 04:40:26 GMT

hello MerryA

sorry, just re-read your need.

correct me if i am wrong, what you need is comparison value each month (the ascension number in same month will be sum-ed).

Create New Table then try this DAX below.

Month =

//Create virtual table to summarize value

var _Summ =

SELECTCOLUMNS(

SUMMARIZE(

'Table',

'Table'[Month],

"Ascension Sum",

SUM('Table'[Accession Number])

"Month Number",

[Month],

"Summ",

[Ascension Sum]

)

//Find value difference each month and zero value for first month

var _Comp =

SELECTCOLUMNS(

_Summ,

[Month Number],

"Comparison",

var _Previous = MAXX(FILTER(_Summ,[Month Number]<EARLIER([Month Number])),[Summ])

Return

IF(

[Month Number]=1,

[Summ]-_Previous

)

//Replacing month number into month name

var _Present =

SELECTCOLUMNS(

_Comp,

[Month Number],

"Month Name",

FORMAT(DATE(1,[Month Number],1),"MMMM"),

[Comparison]

)

Return

_Present

if you need zero value for the first line since there is no value before that, remove conditional if on virtual table _Comp.

Please let me know if this sufficient for your need.

Thank you.