Forum Discussion

Ronak_Mistry's avatar
Ronak_Mistry
Frequent Visitor
3 years ago
Solved

Dax

Hello,

I have a two slicer in Datshboard one is Scheme Start date and End Date.
I want to fine new two date which is pre and post date with add  day of difference of two dates number of dates in Existing scheme start and end date in dax.
i want a measure not coloum.....

Example:   Scheme Start date :22-04-2021               

                 Scheme End Date : 31-5-2021
Number of Days Between Two Dates : 39 Days
I want a Two date Pre Scheme Date : 14-3-2021
Post Scheme Date:09-07-2021

in Measure......
Nd Then i want a Sales Between Two Dates 

 

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    hello im not sure if i understood you correctly but please try:

     

     

     

    Days = 
VAR _min = MIN(Sheet1[Start Date])
VAR _max = MAX(Sheet1[End Date])
Return
COUNTROWS(CALENDAR(_min,_max))-1

     

     

     

     

    make sure to go to format ---> edit interactions and select none on each slicer

     

     

    now to find post and pre:

     

     

    Pre Scheme = MIN(Sheet1[Start Date]) - [Days]

Post scheme = MAX(Sheet1[End Date]) + [Days]

     

     

     

     

    and another example:

     

    then to find your desired sales:

     

    Measure = 
CALCULATE (
    SUM ( salestable[sales] ),
    FILTER (
        salestable,
        salestable[Date] >= pre scheme )
            && salestable[Date] <= psotscheme )
    )
)

     

    dont forget to create a relationship between scheme table and salestable

    if you find it helpful a kudos would be appreciated and consider accepting as solution



2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    hello im not sure if i understood you correctly but please try:

     

     

     

    Days = 
VAR _min = MIN(Sheet1[Start Date])
VAR _max = MAX(Sheet1[End Date])
Return
COUNTROWS(CALENDAR(_min,_max))-1

     

     

     

     

    make sure to go to format ---> edit interactions and select none on each slicer

     

     

    now to find post and pre:

     

     

    Pre Scheme = MIN(Sheet1[Start Date]) - [Days]

Post scheme = MAX(Sheet1[End Date]) + [Days]

     

     

     

     

    and another example:

     

    then to find your desired sales:

     

    Measure = 
CALCULATE (
    SUM ( salestable[sales] ),
    FILTER (
        salestable,
        salestable[Date] >= pre scheme )
            && salestable[Date] <= psotscheme )
    )
)

     

    dont forget to create a relationship between scheme table and salestable

    if you find it helpful a kudos would be appreciated and consider accepting as solution