Calculating YTD/MTD/QTD without date level entry in the source table

[Anonymous]

Hello There

I am wondering if someone could help me if calculating YTD/MTD/QTD is possible with no date level entry in the fact table. Also, we maintain a seperate calendar table with date & other information as follows.

How do we use it for YTD/MTD/QTD calculation?

For instance, we have records given below.

FY/FWK/Branch/Sales Revenue (fact)

2019/01/New York/23441.323

2019/02/New York/17434.323

----

Calendar Date/FY/FWK/FQTR/FMTH/FPD

10-05-2019/2019/48/4/12/2

11-05-2019/2019/48/4/12/2

----

Appreciate your help.

Regards

Kishorekumar

[v-diye-msft]

Hi @Anonymous ,

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,

Dina Ye

[Anonymous]

Hi Dina

Apologies for reaching out late as I was away for couple of weeks. Yes, the solution provided did help our case.

Also, would like to seek help on how the Current week this year/last year/Avg 6 weeks TY & LY (sale week numbers) sales revenue is handled in this situation.

I tried the following and was not successful.

Sales LY TW =

VAR CurrentWeek = SELECTEDVALUE(FACT_WEEKLY_SUMMARY[Fiscal_Week])

VAR CurrentYear = SELECTEDVALUE(FACT_WEEKLY_SUMMARY[Fiscal_Year])

VAR MaxWeekNumber = CALCULATE(MAX(FACT_WEEKLY_SUMMARY[Fiscal_Week]), ALL(FACT_WEEKLY_SUMMARY[Fiscal_Year]))

RETURN

SUMX(

FILTER(ALL(FACT_WEEKLY_SUMMARY),

IF(CurrentYear = 1,

FACT_WEEKLY_SUMMARY[Fiscal_Week] = MaxWeekNumber && FACT_WEEKLY_SUMMARY[Fiscal_Year] = CurrentYear -1,

FACT_WEEKLY_SUMMARY[Fiscal_Week] = CurrentWeek && FACT_WEEKLY_SUMMARY[Fiscal_Year] = CurrentYear -1)),

FACT_WEEKLY_SUMMARY[Sales_Val])

Any help would be great.

Report Requirement: 

** Bold marked -- Working as expected with your solution.

Sales Revenue/TY this week/LY this week/YTD TY/YTD LY/MTD TY/MTD LY/Avg 6 weeks TY/Avg 6 Weeks LY

[v-diye-msft · Accepted solution]

Hi @Anonymous 

I created a sample as yours, we can add the YTD using below measure:

YTD = CALCULATE(SUM('Fact table'[Sales Revenue]),FILTER(ALL('Fact table'),'Fact table'[FWK]<=MAX('Fact table'[FWK])),VALUES('Fact table'[FY]))

When you create the QTD, you can manage the relationship with calendar table to get the Qtr :

And then use the measure below to create QTD and MTD:

QTD = 
CALCULATE(SUM('Fact table'[Sales Revenue]),FILTER(ALL('Fact table'),'Fact table'[FWK]<=MAX('Fact table'[FWK])),VALUES('Table'[FQTR]),VALUES('Fact table'[FY]))

MTD = CALCULATE(SUM('Fact table'[Sales Revenue]),FILTER(ALL('Fact table'),'Fact table'[FWK]<=MAX('Fact table'[FWK])),VALUES('Table'[MTH]),VALUES('Fact table'[FY]))

Pbix attached here for your reference : https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EXl8Q9gpyMxLhmbIXC-t9VcBpNhMfG-ovaAY2XBNf5-Bag?e=Yl9z2x

Best regards,

Dina Ye