Calculate total evergy production from totalizer per day, month, week, etc.

[Anonymous]

I attach an EXCEL file with random increasing values created only for demo reasons. Real data are stored in an SQL Database.

https://1drv.ms/x/s!AtFzkZbNpTaFgYg51j4dFc0vviYp0w?e=Pfz92b

Column A is the sample interval and Column B is the production in KWatts. Apparently during the day and so on this number increases (totalizer).

Here is what I want to achieve:

I want to calculate the difference between the largest value of the present day minus the largest value of the previous day which presents daily production to use it on a visual.

Additionally, I want to summarize those values per week, month, quarter, 6 months year in order to use them in a slicer.

Being a newbie your help will be most valuable.

[Anonymous]

@Greg_Deckler 

I have to reopen the thread because although everything you suggested worked perfectly in  "static" files, the real scenario is in direct query mode.

Through Azure's stream analytics we store sensor values in Azure's SQL Database and through Direct Query we create the reports in Power BI. The problem is that in Direct Query the above mentioned calculations does not work due to known PBI restrictions.

Any further suggestions?

Thanks in advance

[Greg_Deckler]

@Anonymous Try the following, PBIX is attached below signature.

Difference = 
  VAR __Today = TODAY()
  VAR __Yesterday = __Today - 1
  VAR __TodayTable = FILTER('Table', [Column1] >= __Today && [Column1] < __Today + 1)
  VAR __YesterdayTable = FILTER('Table', [Column1]>=__Yesterday && [Column1] < __Today)
  VAR __MaxToday = MAXX(__TodayTable,[Column2])
  VAR __MaxYesterday = MAXX(__YesterdayTable,[Column2])
  VAR __Result = __MaxToday - __Yesterday
RETURN
    __Result

[Anonymous]

@Greg_Deckler 

Thanks for the immediate answer but I really can't understand what your DAX formula does.

May be I wasn't clear enough. For example, in the Excel file I attached there are highlighted the two values (row 24 and row 47) from which I want to calculate the difference by substracting them and so on.

[Greg_Deckler]

@Anonymous So by "present day" you are meaning the current row and you wan't to compare that to the day before? If that's the case, it's a variation on MTBF.  Mean Time Between Failure (MTBF) - Microsoft Power BI Community. Here is an updated calculation (I had an error in it) and updated PBIX attached. Essentially the same code though.

Difference Measure = 
    VAR __Date = MAX('Table'[Column1])
    VAR __Today = DATE(YEAR(__Date),MONTH(__Date),DAY(__Date))
    VAR __Yesterday = __Today - 1
    VAR __TodayTable = 'Table'
    VAR __YesterdayTable = FILTER(ALL('Table'), [Column1]>=__Yesterday && [Column1] < __Today)
    VAR __MaxToday = MAXX(__TodayTable,[Column2])
    VAR __MaxYesterday = MAXX(__YesterdayTable,[Column2])
    VAR __Result = __MaxToday - __MaxYesterday
RETURN
    __Result

[Anonymous]

@Greg_Deckler 

Thank you very much that is exactly what I wanted!

If you please guide me to achieve something like the image I attach.

The demands of the project are about a visual -like the above- which displays energy production values from solar panels.

The slicer, on the left, when selected should display values on the graph by day, week, month, 6 months and yearly. Your measure is very correct and calculates the production per day.

What should I alter to calculate the production according week, month etc. as described above? Is a Calendar table necessary for that?

Thanks again in advance

[Greg_Deckler]

@Anonymous Here are a couple examples:

Difference Measure Month Over Month = 
    VAR __Date = MAX('Table'[Column1])
    VAR __ThisMonthTable = FILTER(ALL('Table'), YEAR([Column1]) = YEAR(__Date) && MONTH([Column1]) = MONTH(__Date))
    VAR __LastMonth = EOMONTH(__Date,-1)
    VAR __LastMonthTable = FILTER(ALL('Table'), YEAR([Column1]) = YEAR(__LastMonth) && MONTH([Column1]) = MONTH(__LastMonth))
    VAR __SumThisMonth = SUMX(__ThisMonthTable,[Column2])
    VAR __SumLastMonth = SUMX(__LastMonthTable,[Column2])
    VAR __Result = __SumThisMonth - __SumLastMonth 
RETURN
    __Result


Difference Measure Year Over Year = 
    VAR __Date = MAX('Table'[Column1])
    VAR __ThisYearTable = FILTER(ALL('Table'), YEAR([Column1]) = YEAR(__Date))
    VAR __LastYear = YEAR(__Date) -1 
    VAR __LastYearTable = FILTER(ALL('Table'), YEAR([Column1]) = YEAR(__LastYear ))
    VAR __SumThisYear = SUMX(__ThisYearTable,[Column2])
    VAR __SumLastYear = SUMX(__LastYearTable,[Column2])
    VAR __Result = __SumThisYear - __SumLastYear 
RETURN
    __Result

[Anonymous]

@Greg_Deckler 

Many Thanks for your answers, some of your proposals worked like a charm.

According to month I've corrected your formula by replacing SUMX with MAXX, but your suggestion according year does not work. Instead of showing the difference between years it displays the max value of each year (namely the last valued measured).

I didn't manage to correct it by myself.

Any further suggestions?