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

[MerryA]

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

[MerryA]

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.

[Irwan · Accepted solution]

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,

        0,

        [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.

[lbendlin]

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.

[MerryA]

Thank you for the explaination!

[Irwan]

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.