Forum Discussion
Sum values current date - sum values last date
I am trying to subtract the values of the current date from the day before the current date.
What I have:
| Month | Day | MCL Value | Measure |
| Feb | 27 | 4.281.365,55 | 0 |
| Feb | 28 | 4.418.949,87 | 4.418.949,87 |
| Mar | 3 | 4.418.949,87 | 4.418.949,87 |
| Mar | 4 | 3.582.675,83 | 3.582.675,83 |
| Mar | 5 | 3.582.675,83 | 3.582.675,83 |
| Mar | 6 | 4.416.023,92 | 4.416.023,92 |
| Mar | 9 | 7.158.087,63 | 7.158.087,63 |
3 Replies
Using date calendar date you can do like
CALCULATE(SUM(Base[MCL Current value SEK]);Dateadd('Date'[Date],-1,DAY))
Or example
last date having data
Measure =
var _max = maxx(allselected(Date),Date[DAte]) // or take Today()
//OR var _max = maxx(allselected(Sale),Sales[DAte]) // or take Today()var _max2 = MAxx(filter(sales,sales[Date]<_max),sales[Date])
return
calculate(sum(sales[value]),Date[Date] =_max2)To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Hi Anonymous
Let me know if you'd like to get below results:
Measure = CALCULATE(MAX('Table'[MCL Value]),FILTER(ALL('Table'),[Day]=MAX('Table'[Day])-1))I want to subtract the sum of the current date with the last date of the current date.
I can't use "(date)-1" because it is not going to be always -1, it depends if we have holidays or weekend.
I need to count automatically the number of one day to another, example:
Column:
var last_date= CALCULATE(maxx(Base;Base[Date]);filter(Base;Base[Date]<EARLIER(Base[Date])))return previous_day_count = DATEDIFF(Base[Date];Base[Dia_Anterior_tag];DAY)This way I can use "previous_day_count" into "-1".And also, the column "MCL Value" is a sum, not a maximum value.
