topic Re: How to get posting date <= current date and clearing date > current date of sales in DAX Commands and Tips

How to get posting date <= current date and clearing date > current date of sales

Anonymous — Fri, 08 Apr 2022 06:40:55 GMT

Hi all,

I am stucked while calculating sales with different conditions. Let me explain in detail

I have a calendar table and a Sales table, and looking for a dax where if i am selecting any date using calendar date which is not equal to current data then the dax should calculate sales where the selected date should less than or equal to posting date and selected date is greater than Clearing date. and Condition type = BSAD (Note:- Posting date & Clearing date & Condition Type are 3 different columns in Sales Table).

Re: How to get posting date <= current date and clearing date > current date of sales

amitchandak — Fri, 08 Apr 2022 17:34:47 GMT

Anonymous , keep date table as an independent table

Try measure like

new measure =
var _max = maxx(allselected(Date),Date[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Psoting Date] <=_mav && 'Table'[Clearing Date] <=_max && [Condition type] = "BSAD" ) )

Re: How to get posting date <= current date and clearing date > current date of sales

tamerj1 — Sat, 09 Apr 2022 20:19:21 GMT

Hi Anonymous

You may try

Sales Amount = VAR SelectedDate = SELECTEDVALUE ( 'Date'[Date] ) RETURN CALCULATE ( SUM ( Table[Amount] ), REMOVEFILTERS ( 'Date' ), Table[Psoting Date] >= SelectedDate, Table[Clearing Date] <= SelectedDate, Table[Condition Type] = "BSAD" )

Re: How to get posting date <= current date and clearing date > current date of sales

Anonymous — Tue, 12 Apr 2022 04:58:14 GMT

i am trying but values are not currect, can you send pbix file for clarity

Re: How to get posting date <= current date and clearing date > current date of sales

tamerj1 — Tue, 12 Apr 2022 05:34:10 GMT

Anonymous

Would you please share some sample data to work with?

Re: How to get posting date <= current date and clearing date > current date of sales

Anonymous — Tue, 12 Apr 2022 06:47:04 GMT

i shared one sample data

and i have one date table also

and i have one date slicer using measure

DateSlicer = IF (SELECTEDVALUE('Date 2'[Date]) = blank(), today(), SELECTEDVALUE('Date 2'[Date]))

and i took a measure for total sales like this,

Total amount = CALCULATE(SUM('Sample Data'[Amount]),FILTER('Sample Data','Sample Data'[Posting Date]<='Sample Data'[DateSlicer]&& 'Sample Data'[Clearing Dat]>[DateSlicer] &&'Sample Data'[Clear doc]="open"))

but,

values are not correct for showing me

please help me with this,

thanks in advanced 😊.

Re: How to get posting date <= current date and clearing date > current date of sales

tamerj1 — Tue, 12 Apr 2022 06:57:33 GMT

Anonymous
Try this

Re: How to get posting date <= current date and clearing date > current date of sales

Anonymous — Tue, 12 Apr 2022 07:02:42 GMT

it is showing like this,

Re: How to get posting date <= current date and clearing date > current date of sales

tamerj1 — Tue, 12 Apr 2022 07:08:26 GMT

Anonymous
Clearing date not clearing count

Re: How to get posting date <= current date and clearing date > current date of sales

Anonymous — Tue, 12 Apr 2022 07:19:11 GMT

Yes,

Sales Amount =

VAR SelectedDate =

SELECTEDVALUE ( 'Date'[Date] )

RETURN

CALCULATE (

SUM ( ZFIT_CRED_AGE[Amount] ),

REMOVEFILTERS ( 'Date' ),

ZFIT_CRED_AGE[Posting Date] <= SelectedDate,

ZFIT_CRED_AGE[Clearing Date] > SelectedDate,

NOT ISBLANK ( ZFIT_CRED_AGE[Clearing Date] ),

ZFIT_CRED_AGE[Clear doc] = "open"

but,

not showing any values

Re: How to get posting date <= current date and clearing date > current date of sales

Anonymous — Tue, 12 Apr 2022 07:42:20 GMT

i do not understand what type of mistake i did

when i write like this

Total Amount = CALCULATE(sum(ZFIT_CRED_AGE[Amount]),filter(ZFIT_CRED_AGE,ZFIT_CRED_AGE[Posting Date] <= [SelectDateSlicer] && ZFIT_CRED_AGE[STATUS]="Open"))

i got these values

Re: How to get posting date <= current date and clearing date > current date of sales

tamerj1 — Tue, 12 Apr 2022 07:57:42 GMT

Anonymous
This is your query

When you filter "Open", then all clearing dates will be blank. Please double check your filtering condition and confirm what exactly do you want to achieve

Re: How to get posting date <= current date and clearing date > current date of sales

Anonymous — Tue, 12 Apr 2022 08:12:37 GMT

this is logic for total sales

Re: How to get posting date <= current date and clearing date > current date of sales

tamerj1 — Tue, 12 Apr 2022 08:38:08 GMT

Anonymous

Sales Amount = VAR SelectedDate = SELECTEDVALUE ( 'Date'[Date] ) RETURN CALCULATE ( SUM ( ZFIT_CRED_AGE[Amount] ), REMOVEFILTERS ( 'Date' ), ZFIT_CRED_AGE[Posting Date] <= SelectedDate, ISBLANK ( ZFIT_CRED_AGE[Clearing Date] ), ZFIT_CRED_AGE[Clear doc] = "open" )

Re: How to get posting date <= current date and clearing date > current date of sales

v-janeyg-msft — Tue, 12 Apr 2022 10:07:04 GMT

Hi, Anonymous

Have you tried this:

Add all or allselected in 'Sample Data'

Total amount = CALCULATE ( SUM ( 'Sample Data'[Amount] ), FILTER ( ALL ( 'Sample Data' ), //ALLselected ( 'Sample Data' ), 'Sample Data'[Posting Date] <= 'Sample Data'[DateSlicer] && 'Sample Data'[Clearing Dat] > [DateSlicer] && 'Sample Data'[Clear doc] = "open" ) )

Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to get posting date <= current date and clearing date > current date of sales

Anonymous — Wed, 13 Apr 2022 05:34:16 GMT

Hi,

finally i got the currect sales,

thanks for response

Total Amount = CALCULATE(SUM(ZFIT_CRED_AGE[Amount]),FILTER(ZFIT_CRED_AGE,ZFIT_CRED_AGE[STATUS]="Open" && ZFIT_CRED_AGE[Postingg Date]<=[SelectDateSlicer]))+CALCULATE(SUM(ZFIT_CRED_AGE[Amount]),FILTER(ZFIT_CRED_AGE,ZFIT_CRED_AGE[STATUS]="clear" && ZFIT_CRED_AGE[Postingg Date]<=[SelectDateSlicer] && ZFIT_CRED_AGE[Clearing Date]>[SelectDateSlicer]))

i have applied above logic

Re: How to get posting date <= current date and clearing date > current date of sales

v-janeyg-msft — Wed, 13 Apr 2022 05:56:07 GMT

Hi, Anonymous

Okay, If your problem has been solved, you can mark your answer as solution to close the thread. Thank you.

Best Regards,
Community Support Team _ Janey

Re: How to get posting date <= current date and clearing date > current date of sales

Anonymous — Thu, 14 Apr 2022 07:18:04 GMT

I will get back to you, as earleir with another quireis for same data

so, please connect with me