Creating a Parallel Period on Snapshots that don't sit against Year Month Quarter for KPI

[DebbieE]

I have a snapshot data set. Where a snapshot of the data is taken 3 times a year.

I have to create KPIs over my data. Which will show the current number of records against the last records from the last snapshot.

from the first snapshot to the next there are 4 months between then

From this to the next snapshot there are 5 month

Then to the next snapshot at the beginning of the year. there has been 3 month between.

So I Cant just use PARALLELPERIOD because of this. Or PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR etc

Does anyone have any advice on some DAX I can build to do Time Based intelligence on this?

[Anonymous]

Hi @DebbieE ,

May I ask if your problem has been solved. If @amitchandak 's reply was helpful, you may consider marking it as solution. If the problem is not yet solved, please feel free to ask us a question.

Best Regards,

Ada Wang

[Anonymous]

Hi @DebbieE ,

Based on the information you have provided, to meet your requirements, you might consider using the DAX custom method to calculate the difference between the current snapshot and the previous snapshot, regardless of irregular intervals. The following is a concise plan of action:

1. Make sure you have a custom date table that includes all snapshot dates and a unique identifier for each snapshot period.

2. Since your intervals are not standard, you will need to manually calculate the intervals between snapshots using DAX. You can create a measure that dynamically identifies the snapshot cycle:

KPI Change =
VAR CurrentSnapshotID =
    MAX ( 'DateTable'[SnapshotID] )
VAR PreviousSnapshotID = CurrentSnapshotID - 1
RETURN
    CALCULATE ( [KPI], 'DateTable'[SnapshotID] = PreviousSnapshotID ) - [KPI]

You will need to adjust the logic to accurately reflect how snapshots are recognised and how you wish to calculate KPIs.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[amitchandak]

@DebbieE , Based on what I got, I would like to create a dim with a distinct snapshot date and a rank column on that

Dates = distinct(Table[Snapshot date])

//additional column in new table 

Rank = rankx(Dates, [Snapshot Date],,asc,dense)

measures

This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Dates ),Dates [Rank]=max(Dates [Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Dates ),Dates [Rank]=max(Dates [Rank])-1))

[DebbieE]

I will have a try of that. thank you