Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
My financial month ends are not calendar date based and they differ each month. I am encountering problems comparing months and years where
1) the Reporting/Accounting month is not calendar based (1st to last day) and
2) the current year month does not start on the same date as the prior year (eg 22 June, 25 July is the financial/reporting month end date so the TD should start on 23 June for July and 26 July for August... ; eg 21 June, 23 July PY vs 22 June, 25 July CY)
See below the table used for Reporting Periods, DIM_AccountingPeriod.
However this wont allow me to mark it as date table so that I can use Time Intelligence functions such as Year to Date (YTD). Month to Date (MTD) etc
then there is a DIM_Time table which can be used for Time Intelligence since it can be marked as date table. It is a proper date table.
I would like to know if Time Intelligence functions will give me the correct MTD, YTD for each month based on these different month start dates. And if the above tables wont, what could be done to achieve that.
Solved! Go to Solution.
@Anonymous , Assuming you have FY Month Start Date, FY Year Start Date , Based on that add follwing columns
Also, you can have FY year, in YYYY format , based on start or end date of year
new columns
Month Rank = RANKX(all('Date'),'Date'[FY Month Start date],,ASC,Dense)
Year Rank = RANKX(all('Date'),'Date'[FY Year Start date],,ASC,Dense)
Day of Year =datediff([FY Year Start date] , [Date],Day) +1
Day of Month=datediff([FY Month Start date] , [Date],Day) +1
measures
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
or
This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[FY Year]=max('Date'[FY Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[FY Year]=max('Date'[FY Year])-1))
YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))
YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi @Anonymous,
You can also take a look at the following blog about use the date function to achieve YTD, MTD calculation without using time intelligence functions:
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
@Anonymous , Assuming you have FY Month Start Date, FY Year Start Date , Based on that add follwing columns
Also, you can have FY year, in YYYY format , based on start or end date of year
new columns
Month Rank = RANKX(all('Date'),'Date'[FY Month Start date],,ASC,Dense)
Year Rank = RANKX(all('Date'),'Date'[FY Year Start date],,ASC,Dense)
Day of Year =datediff([FY Year Start date] , [Date],Day) +1
Day of Month=datediff([FY Month Start date] , [Date],Day) +1
measures
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
or
This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[FY Year]=max('Date'[FY Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[FY Year]=max('Date'[FY Year])-1))
YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))
YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
thank you @amitchandak let me test it
I think I am running into another potential problem re my date table. there are two systems in the DW. defined as such in the date table and fact table. initially i filtered out the one system from the date table to have unique date column, but I will need to report off two systems..
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |