Calculate movement on 'balance' data

[austris]

Hi all,

I'm not a DAX expert by any means - hence, my approach might demonstrate lack of basic fundamentals but I'd still hope you could guide me to the right solution.

I've got fact table 'trs smf' and it's joined to date table 'Calendar'. The join is [trs smf][dt_smf]<-[Calendar][dt].

The fact table are BALANCES (as opposed to movements), i.e., I cannot total differnt dates together to get the monhtly amount, for instance - I have to pick a pre-defined date (dt_smf) that would hold the balance for that entire month. So, the point is to pick the right [dt_smf] transactions to display on the visual. What is 'right' is determined by a user by selecting date granularity, e.g., daily, weekly, monhtly etc.

I've managed to get the right values displayed but I'm struggling to calculate&display the movement, e.g., if user opts to see 'monthly' values - I can get visual to display the balance for June & July correctly, but I cannot figure out how to calculate the movement between the two, i.e., how to get 'previous' month balance if user opted to have 'monthly' and then subrtract it from 'next' month, or how to get 'previous' week balance, if user opted to view data 'weekly' etc.

This is what I've done:
The date granularities are:
Daily, Weekly, Monthly, Quarterly, Yearly
The [trs smf] has columns that contain date IF they're to be used for the selected granularity otherwise they're blank. These are the columns:
dt_day
dt_we
dt_m
dt_q
dt_y
e.g., if the row IS TO BE USED for 'weekly' granularity, then dt_we would have a date otherwise it would be blank.

I've also created a calculation group with according items, e.g.:
========================
Yearly =
CALCULATE(
SELECTEDMEASURE(),
FILTER(
'trs SMF',
not ISBLANK(('trs SMF'[DT_Y]))
)
)
Weekly =
CALCULATE(
SELECTEDMEASURE(),
FILTER(
'trs SMF',
not ISBLANK(('trs SMF'[DT_WE]))
)
)
... and the same for daily, monthly, quarterly
========================
This works to display the actual balances for the chosen granularity BUT how would I get to display the movement from previous year/quarter/month/week/day - depending on the date granularity chosen by the iuser?

I don't think I can use 'visual calculations' as I need to use 'fields' parameter.

I hope it makes snese what I'm after and you could point me in the right direction...

[Anonymous · Accepted solution]

Hi @austris ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a date dimension table

Period

Daily

Weekly

Monthly

2. Create the measures as below

Daily movement =
VAR _period =
    SELECTEDVALUE ( 'Period'[Period] )
VAR _daily =
    SELECTEDVALUE ( 'Table'[dt_day] )
VAR _predaily =
    CALCULATE (
        MAX ( 'Table'[dt_day] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_day] < _daily )
    )
VAR _predvalue =
    CALCULATE (
        SUM ( 'Table'[amt] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_day] = _predaily )
    )
RETURN
    IF ( NOT ( ISBLANK ( _predaily ) ), SUM ( 'Table'[amt] ) - _predvalue )

Weekly movement = 
VAR _period =
    SELECTEDVALUE ( 'Period'[Period] )
VAR _weekly =
    SELECTEDVALUE ( 'Table'[dt_we] )
VAR _preweek =
    CALCULATE (
        MAX ( 'Table'[dt_we] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_we] < _weekly )
    )
VAR _prewvalue =
    CALCULATE (
        SUM ( 'Table'[amt] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_we] = _preweek )
    )
RETURN
    IF ( NOT ( ISBLANK ( _preweek ) ), SUM ( 'Table'[amt] ) - _prewvalue )

Monthly movement = 
VAR _period =
    SELECTEDVALUE ( 'Period'[Period] )
VAR _monthly =
    SELECTEDVALUE ( 'Table'[dt_m] )
VAR _premonth =
    CALCULATE (
        MAX ( 'Table'[dt_m] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_m] < _monthly )
    )
VAR _premvalue =
    CALCULATE (
        SUM ( 'Table'[amt] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_m] = _premonth )
    )
RETURN
    IF ( NOT ( ISBLANK ( _premonth ) ), SUM ( 'Table'[amt] ) - _premvalue )

3. Create a visual

Best Regards

[austris]

@DataInsights , @Anonymous ,

Thanks for your responses - you both put me on the right track - both in re-bulding (normalizing) the data model  (basically I'm now unpivotting the various granularity columns into a single one and sure it makes the DAX simpler!!) and how to write the movement measures.

Thanks!!

[DataInsights]

@austris,

One approach is to calculate the activity using a calculated column in the fact table. Create a SUM measure using the activity column, and use a field parameter to control the aggregation displayed in the visual (Calendar fields day, month, etc.).

activity = 
VAR vDate = 'trs smf'[dt_smf]
VAR vPrevDate =
    CALCULATE (
        MAX ( 'trs smf'[dt_smf] ),
        ALL ( 'trs smf' ),
        'trs smf'[dt_smf] < vDate
    )
VAR vBalance = 'trs smf'[balance]
VAR vPrevBalance =
    CALCULATE (
        SUM ( 'trs smf'[balance] ),
        ALL ( 'trs smf' ),
        'trs smf'[dt_smf] = vPrevDate
    )
VAR vResult = vBalance - vPrevBalance
RETURN
    vResult

[austris]

Thanks @DataInsights !

Apologies, if my description of the data structure was misleading.

This is how the fact looks and I wasn't sure if the 'activity' column would still work as depending on the user's choice on a slicer (calculation group) different rows constitute as previous:

dt_smf	amt	dt_day	dt_we	dt_m
02/Jul/2024	824			02/Jul/2024
02/Aug/2024	60			02/Aug/2024
09/Aug/2024	319		11/Aug/2024
15/Aug/2024	4	15/Aug/2024
16/Aug/2024	48	16/Aug/2024
17/Aug/2024	16	17/Aug/2024	18/Aug/2024
17/Aug/2024	503	17/Aug/2024	18/Aug/2024

e.g., if user selects 'daily' (calculation group item), then movement would need to be calculated between dates as displayed in [dt_day], i.e., in this particular instance would need to:

- total last 2 rows (503+16) (because [dt_day]=Aug 17th and then 3rd row from bottom (previous existing [dt_day] would need to be subtracted=(503+16)-48 would be the movemennt for the 17th of August.

- take 3rd row from bottom and subtract 4th row from bottom (dt_day Aug 16th minus dt_day Aug 15th) = 48-4 and that would be movement for 16th of Aug

- for the 15th of August the movement would be =4 as there are no dt_day dates before it.

If the user selects 'weekly', then the movement would be=last two rows together minus the 3rd row from the top, because last to rows feed into week-ending [dt_we]=Aug 18th and previous existing (non-blank) [dt_we] is 3rd row from the top where the week-ending date is 11th of August, so, the movement would be=(503+16)-319 beause user selected 'weekly' and therefore the column [dt_we] tells me what rows to total and subtract from each other.

Which means I cannot take previous max dt_smf as the user-choce determins what rows from the fact should be actually looked at.

And I was hoping that the 'calculation group items' that I've introduced would do the row-filtering task (which they do for balances) but I cannot figure it out if it's even possible to use the same 'calcualtion gorup items' to still filter the 'weekly' or 'daily' or 'yearly' etc. rows AND to calculate PREVIOUS values to get the movement...

Have I managed to clarify...?

[DataInsights · Accepted solution]

@austris,

Thanks for the clarification. My approach would be to model the data properly so that you have activity for each day in the fact table. This will simplify your model and improve performance. You should be able to adapt the DAX examples provided, and push that logic to Power Query (or SQL if possible). Better modeling, simpler DAX. 🙂

[Anonymous · Accepted solution]

Hi @austris ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a date dimension table

Period

Daily

Weekly

Monthly

2. Create the measures as below

Daily movement =
VAR _period =
    SELECTEDVALUE ( 'Period'[Period] )
VAR _daily =
    SELECTEDVALUE ( 'Table'[dt_day] )
VAR _predaily =
    CALCULATE (
        MAX ( 'Table'[dt_day] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_day] < _daily )
    )
VAR _predvalue =
    CALCULATE (
        SUM ( 'Table'[amt] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_day] = _predaily )
    )
RETURN
    IF ( NOT ( ISBLANK ( _predaily ) ), SUM ( 'Table'[amt] ) - _predvalue )

Weekly movement = 
VAR _period =
    SELECTEDVALUE ( 'Period'[Period] )
VAR _weekly =
    SELECTEDVALUE ( 'Table'[dt_we] )
VAR _preweek =
    CALCULATE (
        MAX ( 'Table'[dt_we] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_we] < _weekly )
    )
VAR _prewvalue =
    CALCULATE (
        SUM ( 'Table'[amt] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_we] = _preweek )
    )
RETURN
    IF ( NOT ( ISBLANK ( _preweek ) ), SUM ( 'Table'[amt] ) - _prewvalue )

Monthly movement = 
VAR _period =
    SELECTEDVALUE ( 'Period'[Period] )
VAR _monthly =
    SELECTEDVALUE ( 'Table'[dt_m] )
VAR _premonth =
    CALCULATE (
        MAX ( 'Table'[dt_m] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_m] < _monthly )
    )
VAR _premvalue =
    CALCULATE (
        SUM ( 'Table'[amt] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[dt_m] = _premonth )
    )
RETURN
    IF ( NOT ( ISBLANK ( _premonth ) ), SUM ( 'Table'[amt] ) - _premvalue )

3. Create a visual

Best Regards