Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    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

3 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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