Forum Discussion

dimitrischo's avatar
dimitrischo
Helper I
9 years ago
Solved

Week on Week , Month on Month Analysis

Hi,   I am a noob to power bi and i am trying to give reports that they are going to have week on week analysis and month on month.   This moment i Have all of my data in excel which are rows fro...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi dimitrischo,

     

    To calculate the difference month on month and week on week, you can create a calendar table, and build the relationship based on date value between calendar table and fact table. Then create measures like below:

     

    MOM =
    VAR PreMonth = CALCULATE( MAX( 'Calendar'[Month] ) ) - 1
    return
    ( IF(CALCULATE( MAX( 'Calendar'[Month] ) )=1,BLANK(),
     (SUM('Fact'[Sales])-CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), MONTH( 'Fact'[Date] ) = PreMonth ) ))/CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), MONTH( 'Fact'[Date] ) = PreMonth ) )
    ))

     

    WOW =
    VAR PreWeek = CALCULATE( MAX( 'Calendar'[Week] ) ) - 1
    return
    (IF(CALCULATE( MAX( 'Calendar'[Week] ) )=1,BLANK(),
     (SUM('Fact'[Sales])-CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), WEEKNUM( 'Fact'[Date] ) = PreWeek ) ))/CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), WEEKNUM( 'Fact'[Date] ) = PreWeek ) )
    ))

     

    But currently we are not able to add indicators in a table, you need to create Power Pivot model and KPIs in a workbook firstly then import it to Power BI Desktop. See: Import and display KPIs.

     

    Best Regards,
    Qiuyun Yu

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Hi dimitrischo,

 

To calculate the difference month on month and week on week, you can create a calendar table, and build the relationship based on date value between calendar table and fact table. Then create measures like below:

 

MOM =
VAR PreMonth = CALCULATE( MAX( 'Calendar'[Month] ) ) - 1
return
( IF(CALCULATE( MAX( 'Calendar'[Month] ) )=1,BLANK(),
 (SUM('Fact'[Sales])-CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), MONTH( 'Fact'[Date] ) = PreMonth ) ))/CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), MONTH( 'Fact'[Date] ) = PreMonth ) )
))

 

WOW =
VAR PreWeek = CALCULATE( MAX( 'Calendar'[Week] ) ) - 1
return
(IF(CALCULATE( MAX( 'Calendar'[Week] ) )=1,BLANK(),
 (SUM('Fact'[Sales])-CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), WEEKNUM( 'Fact'[Date] ) = PreWeek ) ))/CALCULATE( SUM( 'Fact'[Sales] ), FILTER( ALL('Fact'), WEEKNUM( 'Fact'[Date] ) = PreWeek ) )
))

 

But currently we are not able to add indicators in a table, you need to create Power Pivot model and KPIs in a workbook firstly then import it to Power BI Desktop. See: Import and display KPIs.

 

Best Regards,
Qiuyun Yu

 

dimitrischo's avatar
dimitrischo
Helper I
9 years ago

Hi v-qiuyu-MSFT,

The info that you gave me are great! but i have only one issue. on my row data i have some categories. and when i apply a filter on report with this column doesn't calculate right. How can i Fix that?  I think this is happening because of the filter All.