calculate sum till date selected

[Anonymous]

Dear team, 

Please help me with problem scenario:

Date(dd/mm/yyyy)                     Sales 

1/1/2020                                      50

2/1/2020                                      60

3/1/2020                                      80

4/1/2020                                      45

5/1/2020                                      70

Case 1: If user selects date as 3/1/2020: then it should display sales sum till 3/1/2020  ( 50+60+80) = 190

Case 2:  if user selects a date range ( 1/1/2020-4/1/2020). then it should add sales value end date range, i.e. till 4/1/2020 = (50+60+80+45) = 235

Problem 2:

As per the above data, if user selects a date, it should tell me how many days have passed in year till that date(including that date)

For example, if user selects 10/1/2020), value should be 10 Days 

b)if user selects date range, then number of days passed till end date range should be displayed

Example : 2/1/2020- 8/1/2020 

then display 8Days  as per 8/1/2020

Thanks in advance!

[v-jayw-msft]

Hi @Anonymous ,

Check this measure.

Measure = 
var a = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))
var b = CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])&&'Table'[Date]>=MIN('Table'[Date])))
return
IF(COUNTROWS('Table')=1,a,b)

Result would be shown as below.

 

Best Regards,

Jay

[Anonymous]

@v-jayw-msft 

Thankyou will try this once and share my feedback.

If possible share the solution for second half of my question as well that how to find the number of day from a respective date

For example:

5/1/2020 - 5th Day

29/12/2020 - 364th Day

A logic which tells how many days have passed including the date which has been selected.

Thankyou

[v-jayw-msft]

Hi @Anonymous ,

Check this measure for your second question.

Measure = 
var mindate = CALCULATE(MIN('Table'[date]),ALL('Table'))
return
IF(COUNTROWS('Table')=1,DATEDIFF(mindate,SELECTEDVALUE('Table'[date]),DAY)+1,DATEDIFF(MIN('Table'[date]),MAX('Table'[date]),DAY)+1)

Result as below.

 

Best Regards,

Jay

[jonnyA]

I have a similar question.  i am trying to sum the "# of day selected".  I tried this formula you commented, but it did not work for me ... 

Measure = var mindate = CALCULATE(MIN('Table'[date]),ALL('Table')) return IF(COUNTROWS('Table')=1,DATEDIFF(mindate,SELECTEDVALUE('Table'[date]),DAY)+1,DATEDIFF(MIN('Table'[date]),MAX('Table'[date]),DAY)+1)

[Anonymous]

@v-jayw-msft 

Will this logic works if I have 2 year of date? 2019 and 2020

[v-jayw-msft · Accepted solution]

@Anonymous ,

Yes. Based on my test it will.

[amitchandak]

@Anonymous , for first one . Try with a date table

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(Sales[Sales Date])))

second one

days measure =

var _max =selectedvalue(Date[Date])

return

datediff( date(year(_max) ,1,1), _max , Day)

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

@amitchandak 

for second part, I tried this but my measure is showing blank value .What am I missing here ?

I have calendar table only which shows month and year.

Please help me