Visual with different granularity how to switch?

[jaryszek]

Hi Guys,

 I have model like that: 

and In a model currently i have visuals filtered by Daily Amortized Costs . I am using top 5 filtered within line chart visuals. 
Total Amortized Cost coming now from Daily Fct table:

How to switch easily this visual to use Total Cost for monthly table and filter visual by top 5 but from montly fct table? (i suppose it will be proper set up).

Best,
Jacek

[DataNinja777]

Hi @jaryszek ,

For the amortization of the capitalized expenses and to handle the different granurality such as daily, monthly, quarterly, and yearly with ease, you can use the disconnected calendar table and the amortization schedule table which has the information of 

• Capitalized expenses ($)
• Amortization start date
• Amortization end date (deduced from amortization period)

Then you can write an amortization measure for both period expenses and cumulative life time to date expense like below:

Amortization Expense (by Period) = 
VAR ctxMinDate = MINX ( VALUES ( 'Calendar'[Date] ), 'Calendar'[Date] )
VAR ctxMaxDate = MAXX ( VALUES ( 'Calendar'[Date] ), 'Calendar'[Date] )
-- Anchor to month-ends so each month counts as one unit (inclusive)
VAR ctxStartM  = EOMONTH ( ctxMinDate, 0 )
VAR ctxEndM    = EOMONTH ( ctxMaxDate, 0 )
RETURN
SUMX (
    'Capitalized expense amortization',
    VAR startM = EOMONTH ( 'Capitalized expense amortization'[Amortization start date], 0 )
    VAR endM   = EOMONTH ( 'Capitalized expense amortization'[Amortization End Date], 0 )
    VAR lifeMo = DATEDIFF ( startM, endM, MONTH ) + 1
    VAR perMo  = DIVIDE ( 'Capitalized expense amortization'[Capitalized expenses ($)], lifeMo )
    VAR overlapStart = MAX ( startM, ctxStartM )
    VAR overlapEnd   = MIN ( endM,   ctxEndM   )
    VAR monthsInContext =
        IF ( overlapEnd < overlapStart, 0,
             DATEDIFF ( overlapStart, overlapEnd, MONTH ) + 1
        )
    RETURN perMo * monthsInContext
)

Amortized Amount (Cumulative) = 
VAR asOfDate = MAXX ( VALUES ( 'Calendar'[Date] ), 'Calendar'[Date] )
RETURN
SUMX (
    'Capitalized expense amortization',
    VAR startM = EOMONTH ( 'Capitalized expense amortization'[Amortization start date], 0 )
    VAR endM   = EOMONTH ( 'Capitalized expense amortization'[Amortization End Date], 0 )
    VAR lifeMo = DATEDIFF ( startM, endM, MONTH ) + 1
    VAR elapsedM =
        IF ( asOfDate < startM, 0,
             DATEDIFF ( startM, MIN ( asOfDate, endM ), MONTH ) + 1
        )
    RETURN
        DIVIDE ( 'Capitalized expense amortization'[Capitalized expenses ($)] * elapsedM, lifeMo )
)

The resultant amortization schedule is totally flexible with respect to the calendar granurality filtering.  

When yearly filtering is applied, amortization schedule will it will look like below:

When quarterly filtering is applied, amortization schedule will it will look like below:

When monthly filtering is applied, amortization schedule will look like below:

By using the amortization table of start and end date in combination with the disconnected calendar table, you can write a flexible measure which frees you from the granularity issue you've noted.  I use for amortization this type of measure for flexibility and scalability of maintaining accounting records. 

I attach a pbix file for your reference.

Best regards,

[jaryszek]

Thank you very much! 

It is a different approach not demanding Daily and Monthly fact tables. Very interesting. 
I do not now if I will use it in my destination model, i have daily and monthly tables already provided by engineering team.

Best,
Jacek

[Pragati11]

HI @jaryszek 
I think you have a usecase for Field Parameters here.

Check if this helps:

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

[jaryszek]

thank you, i do not think so it will switch top 5 visual filter also plus it is changing like category, what about measure change? 

Best,
Jacek

[v-tejrama · Accepted solution]

Hi @jaryszek ,

Thanks for reaching out to the Microsoft fabric community forum.

You can address this by utilizing Field Parameters in Power BI, which enable dynamic switching between different fields or measures within a single visual. This approach is ideal for scenarios where you need to display data at varying granularities, such as daily or monthly. By including both your daily and monthly measures within one parameter, you can use it in your visual and add it as a slicer, allowing users to toggle between views without creating separate visuals. While Field Parameters do not automatically detect granularity, they offer a straightforward manual solution. If you require automatic adjustments when drilling through a hierarchy, consider creating a DAX measure with the ISINSCOPE function to modify calculations based on the visual’s context. For most situations, Field Parameters remain the most practical and flexible option. For step-by-step guidance, please refer to Microsoft’s documentation: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

Please find the attached PBIX and Screenshort file for your reference.

Best Regards,
Tejaswi.
Community Support

[jaryszek]

Hello,

Thank you! 
I am analyzing this model and it is not working for me.

I tried to see Month granularity and still is showing Day:

What I am doing wrong? 

Best,
Jacek

[v-tejrama]

Hi @jaryszek ,

Appreciate you sticking with this and sharing more details it really helps! You’re totally right, the visual’s probably using the default Date hierarchy, which is why it keeps drilling down to days. To sort it out, just remove that hierarchy from the X-axis and add your field parameter with both the day and month fields. That way, you can easily switch between daily and monthly views with the slicer, without it defaulting to days.

Give it a try and let me know how it works out or if you run into anything else.

Best Regards,
Tejaswi.
Community Support

[jaryszek]

Worked perfectly, 

Thank you very much!

[Pragati11]

Hi @jaryszek 

Field Parameters does allow you to switch between measures as well.

Check this blog here: https://endjin.com/blog/2022/06/how-to-dynamically-switch-between-measures-in-power-bi-visuals-with-...

Also check the existing thread below:

https://community.fabric.microsoft.com/t5/Desktop/How-to-use-Top-N-visual-filter-with-Field-paramete...