Calculations in Direct Query mode according power production per day, month, etc.

[Anonymous]

Although I've managed to achieve DAX calculations with static Excel files according the following problem, I couldn't achieve this in Direct Query mode. Here is the scenario:

Through Azure's Stream Analytics we store in SQL Server Database some values referring to solar panel energy production.

Sampling rate is about every hour. We want to calculate the daily (weekly, monthly, yearly... ) production by substracting the maximum of each time value with the maximum of the previous same period.

Here is a screenshot of Power Query editor

You can the last value recorder in 11/02/2023. We want to substract this value from the respective last value of 12/02/2023 in order to have the daily production.

Does anyone has any ideas?

Thanks in advance

[Anonymous]

@amitchandak 

Sorry for the delayed answer... Thank you for your suggestions.

Your proposals

MTD = CALCULATE(Max(Table[Value]),DATESMTD('Date'[Date]))
last MTD = CALCULATE(Max(Table[Value]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

are the ones I do want, but I have some further questions.

The real scenario is this:

I have ten (10) different production values, INV1, INV2, INV3.... INV10. I do calculate the MTD value and the Last MTD value as suggested for INV1, in a new measure I calculate the MTD - Last MTD difference to have as a result the monthly production of INV1. This difference is the final number I want to have it on a visual, namely the energy production.

• Do you believe that the final solution is to have the same for calculations for INV2, INV3 and so on? (Three for each one?).
• Have in mind that I have to calculate this for day, month, 6months, year.
• The final visual (pretty draft) is the one I have below. A slicer with date values (daily, monthly, weekly, yearly etc.) and a column chart respectively.

 I also remind that data are acquired in direct query mode.

Thanks in advance.

[amitchandak]

@Anonymous , Based on what I got, You can use time intelligence

MTD = CALCULATE(Max(Table[Value]),DATESMTD('Date'[Date]))
last MTD = CALCULATE(Max(Table[Value]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Same way you can get Qtr, year

You can also try offset function

Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s