Time Intelligence : YoY Closing Balance Look with drilldown on date hierarchy
Have a simple table like a date and amount. The amount is NOT purchase activity but a mortgage ENDING BALANCE. hence if I sum it across time periods we will get values that are not true ending balance.
- show ending balance and its sameperiodlastyear() balance
- along with drill down behaviour by year and month
THe problem is
1. how do you calculate the ending balance where my context could be month or year in teh same report.
2. if im in monthly timescale, the ending balance should be based on monthly value
3. if im in yearly timescale, they should be ending balance based on yearly value
Appreciate your response. Will try to understand the logic for each of your steps. Looks like you are an expert with DAX. I want to ask a follow up question. I can use CALCULATE function well but I am sure there is grey area in my understandng. I am confused on the arguments and return types for Filters in CALCULATE function. I understand the way the contexts are changed. But I want to understand the arguments and return types procesing.
Can you give me a good reference which explains CALCULATE function in a simplistic manner? Or your explanation if you have time?