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

[Anonymous]

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).

[tamerj1]

@Anonymous 

Would you please share some sample data to work with?

[Anonymous]

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 😊.

[tamerj1]

@Anonymous 
Try this

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

[Anonymous]

it is showing like this,

[tamerj1]

@Anonymous 
Clearing date not clearing count

[Anonymous]

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

[Anonymous]

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

[tamerj1]

@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 

[Anonymous]

this is logic for total sales

[v-janeyg-msft · Accepted solution]

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.

[Anonymous]

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

[v-janeyg-msft]

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

[Anonymous]

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

so, please connect with me 

[tamerj1]

@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"
    )

[tamerj1]

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"
    )

[amitchandak]

@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" ) )

[Anonymous]

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