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

     

johanmeulenkamp's avatar
johanmeulenkamp
Frequent Visitor
9 years ago

Hi there! 

 

  • I tried this solution as well, but it didn't work out (see attached example and your solution). I'm learning some DAX-coding now, so I'm not super experienced yet. WOW makes the wrong calculation. 
  • I would like to and have the Power BI-files availabe to test and check. 
    Your Example
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 ) )
))

My Example
WOW = 
VAR PreWeek = CALCULATE( MAX( 'Calendar'[Week] ) ) - 1
return
(IF(CALCULATE( MAX( 'Calendar'[Week] ) )=1;BLANK();
	(SUM('Sessions'[Traffic])-CALCULATE( SUM( 'Sessions'[Traffic] ); FILTER( ALL('Sessions'); WEEKNUM( 'Sessions'[Date]) = PreWeek ) ))/CALCULATE( SUM( 'Sessions'[Traffic] ); FILTER( ALL('Sessions'); WEEKNUM( 'Sessions'[Date] ) = PreWeek ) )
))

 

Could you please explain me what went wrong? 

Cristina101083's avatar
Cristina101083
Advocate II
9 years ago

I dont have this option of PreWeek.

I can see Prewek, but it tells me that it does not work. 

 

see my formula:

 

WOW = VAR Prewek=calculate(max(DimDate[Week]))-1
return
(if(calculate(max(DimDate[Week]))=1,BLANK(),
[DI Rigs]-calculate([DI Rigs],filter(all('Drilling Info_Final_Data'),WEEKNUM('Drilling Info_Final_Data'[Friday_Date])=Prewek)))/CALCULATE([DI Rigs]),FILTER(all('Drilling Info_Final_Data'),
WEEKNUM('Drilling Info_Final_Data'[Friday_Date])=Prewek))

 

Can I get some help?