Consumption measures and filter for 2 tables

[Anonymous]

Hello 🙂 

For some context, I have two tables as you can see below. They are datas from energy counters that are in different locations (careful, the dates are in french format) : 

Data_Site_A

Data_Site_B

My goal is twofold : 

- Frist, I want to measure the "consumption" that every counter has measured. I didn't know how to do it in M (if you know how to do it it would be cool though), so I created the measures using DAX's formula PARALLEL PERIOD (knowing that my datas will arrive every month, my period would be 1 month) :

Consumption_A = 
CALCULATE(
    SUM(Data_site_A[Index]),
    PARALLELPERIOD(Data_site_A[Date],0,MONTH)) 
-
CALCULATE(
   SUM(Data_site_A[Index]),
    PARALLELPERIOD(Data_site_A[Date],-1,MONTH)) 

 Result of the measure (the "consumption" for the first period is false but I don't care) : 

-Then I want to see the evolution of all my "consumption" in 1 chart and being able to switch from one "site" to another. That is to say I want a chart having both measures of "consumptions" and a filter for the choice of which "site" I want.

Here is a view of the chart I want but with both measures in it (I should have 2k in may though...) : 

I tried to link the two tables with two others tables : one with the list of my sites A,B,C... and the other one with a table of dates to have only one reference of dates in my X-axis. And it didn't worked when I tried to switch from one site to another (the links didn't want to work). 

Could you help me with this ? 

Sincerily, 

Enzo

[Anonymous · Accepted solution]

Hi  @Anonymous  ,

Here are the steps you can follow：

1. Create calculated table. 

Table = UNION('Data_Site_A','Data_Site_B')

2. Create measure.

Consumption =
var _1=
SUM('Table'[Index])

var _lastmonth=
EOMONTH(MAX('Table'[Date]),-1)

var _2=
CALCULATE(
    SUM('Table'[Index]),
FILTER(ALL('Table'),'Table'[Date]>=DATE(YEAR(_lastmonth),MONTH(_lastmonth),1)&&'Table'[Date]<=_lastmonth&&'Table'[Site]=MAX('Table'[Site])))

return
_1-_2

3. Use Site as the slicer, use Clusterd column chart, Date as Axis, and Consumption as Values.

4. Result:

Choose Site=A

Choose Site=B

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello M. Yang, 

Sorry for the late response. 

Thank you very much for your help, it seems to work perfectly ! I've found a way to calculate also the consumptions via Power Query, it's just another way. 

Have a wonderful day. 

Enzo

[Anonymous]

Hi @Anonymous 

Before doing any serious PBI development, I think you should first read this: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

I write this because I can see 2 tables where in fact there should only be one.