values on the bases of selecting dates

[Bakhtawar]

For suppose

my date range is 1st feb to 28th feb
I can select date filter like 5th feb to 28th feb then I need count of 1st feb to 5th feb in the results. How could I get through dax.

I used only one date and drag in slicer .. and there is two columns in table .. date and values

[v-alq-msft]

Hi, @Bakhtawar 

I think I misunderstand your thought. I wonder if you want to sum the value for unselected dates. Here is my sample.

Table:

You may create a measure as follows.

NotSelectedCount = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        NOT( 'Table'[Date]) in FILTERS('Table'[Date])
    )
) 

Result:

If I misunderstand your thoughts, please show me your sample data and expected result. Do mask sensitive data before uploading. Thanks

Best Regards

Allan

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

[Bakhtawar]

@v-alq-msft @Ashish_Mathur 

i have data like this

Date	values
10/18/2019	24
10/23/2019	25
10/23/2019	26
11/13/2019	27
11/13/2019	28

there is zig zag dates..

so when i drag this date column in slicer

suppose if i select date from 10/23/2019 to 11/13/2019 then output should be 1 beacuse from begining of month of october

10/1/2019 to 10/23/2019 only 10/18/2019 is inculueded in data .. so output should be 1

and if you select date 11/13/2019 then output should be be 3 beacuse from the this date 11/13/2019 only 3 previous dates are avaible

[v-alq-msft · Accepted solution]

Hi, @Bakhtawar 

Based on the description, I created my data to reproduce your scenario.

Table:

You may create a measure as follows.

Count = 
var _mindate = 
CALCULATE(
    MIN('Table'[Date]),
    ALL('Table')
)
var _maxdate = 
CALCULATE(
    MIN('Table'[Date]),
    FILTERS('Table'[Date])
)
var _result = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        'Table'[Date]>=DATE(YEAR(_mindate),MONTH(_mindate),1)&&
        'Table'[Date]<_maxdate
    )
)
return 
IF(
    ISBLANK(_result),
    0,
    _result
)

Result:

Best Regards

Allan

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

[Bakhtawar]

@v-alq-msft  this help me out thanks

but i have another field account number .. when i select dates and did not select account number then worked fine

but when i select dates and select account number then expected output is wrong

wheree as that account number field is in the same table.. so where i pass account number ?

[v-alq-msft]

Hi, @Bakhtawar 

Please show us your sample data and expected result. Do mask sensitive data before uploading.

Best Regards

Allan

[Bakhtawar]

@v-alq-msft  probelm solved thanks

[Ashish_Mathur]

Hi,

Setup the PBI file like i have mentioned in my previous post.  Write this measure

Count of rows from beg of month to first date of selected date range = CALCULATE(COUNTROWS(Data),DATESBETWEEN(Calendar[Date],EOMONTH(MIN(Calendar[Date]),-1)+1,MIN(Calendar[Date])-1))

If the date selected in the slicer is 11/13/2019, then the result should be 0 because there is no entry from 11/1/2019 to 11/12/2019.

[Ashish_Mathur]

Hi,

Create a Calendar Table.  Build a relationship from the Date columm of the Data Table to the Date column of the Calendar Table.  Build a slicer from the Date column of the Calendar Table and write this measure

Values = SUM(Data[Value])

Values from beg of month to first date of selected date range = CALCULATE([Values],DATESBETWEEN(Calendar[Date],EOMONTH(MIN(Calendar[Date]),-1)+1,MIN(Calendar[Date])))

Hope this helps.

[amitchandak]

@Bakhtawar 

Make sure you have date calendar and try like

Measure =
var _max = minx(Date,Date[Date])
var _min= date(year(_max),month(_max),1)
return
calculate(count(table[Value],filter(all(Date),Date[Date]<= _max && Date[Date]>= _min))

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/

Appreciate your Kudos

[Ashish_Mathur]

Hi,

What result would you expect if you select the date range as 10 Feb to 20 Mar?

[Bakhtawar]

---

@Ashish_Mathur wrote:

Hi,

What result would you expect if you select the date range as 10 Feb to 20 Mar?

---

@Ashish_Mathur  count from 1 to 10 feb

[v-alq-msft]

Hi, @Bakhtawar 

Based on your description, I created data to reproduce your scenario.

Table:

Then you may create two measures. One is for count of selected values and another is for count of  not selected values.

SelectedCount = 
CALCULATE(
    DISTINCTCOUNT('Table'[Date]),
    FILTERS('Table'[Date])
)

NotSelectedCount = 
CALCULATE(
    DISTINCTCOUNT('Table'[Date]),
    FILTER(
        ALL('Table'),
        NOT( 'Table'[Date]) in FILTERS('Table'[Date])
    )
) 

Result:

Best Regards

Allan

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

[Saurabh8437]

So you want sum of values in time period excluding selected period?