Forum Discussion

justivan's avatar
justivan
Icon for Helper II rankHelper II
6 years ago
Solved

DAX measure to filter based on 2 dates

Hello PBI Community,   I'm new to Power BI and I've been trying to create a dashboard for our company to use. As I'm working for a Destination Management Company, the report is normally summarized ...
Need Help
  • tex628's avatar
    tex628
    6 years ago

    justivan 

    This was actually abit more complicated than i initially understood 🙂 But i did some things and ths is the result:



    Something i noticed, as you can see in the images above there is an increase in January that i didn't expect. This is because there are transactions like this: 



    Either way this is what i did, 

    First of all i created a duplicate of your Date table and made sure that this table did not have any active relationships:



    Following this I changed ResDate in the matrix to Date_2[Date] and added a slicer on the same field:

    Finally I changed the DAX on the Cumulative measure:

    Cumulative Pax = 
VAR mDate = MAX('Date _ 2'[Date])
Return
CALCULATE([PaxByInDate];
Bookings[ResDate] < mDate
)


    Try this and get back to me, i hope we're on the right track!

    Br,
    J



justivan's avatar
justivan
Icon for Helper II rankHelper II
6 years ago

Hi amitchandak and tex628 

Thanks for your responses.

I'm sorry but I think I wasn't clear enough on what output I need. So basically, the way the report will be shown is still based on 'Arrivals' per month but we need to see the movement ideally per week. I did a sample output in excel. Say for example today is Sep 8, 2019, we have the below figures with a total of 324,509. 1 week prior, we had 313,109. So the added figure was roughly 11K that's spread from September to December arrivals. No changes in prior months as those are actuals already since it's in the past. So basically it's like showing the accumulated data.

Intended output to be shown in matrix and are chart for visuals

I have this measure from a tutorial that I watched where I got the idea. Except this one was only based on 'Order Date' and 'Totals'. Whereas, what my intended output is to show the monthly arrivals based on reservation date.

 

 

Cumulative Sales = CALCULATE( [Total Sales],
    FILTER( ALLSELECTED( Dates ),
        Dates[Date] <= MAX( Dates[Date] ) ))

 

 

Does it make sense to create two calendar tables where one is for arrival date and one is for reservation date? I'm thinking of trying it once I get home.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Same dates dimension you have to join with both dates(date without timestamp). One join will be inactive. And then as per need use userelation to activate the join. I have given an example and link in the last update.

     

    One more case when we want one formula to run on both dates. We can have a slicer from the other date too. Typically dates allow you date hierarchy. But most needed we can have two date dimensions.

     

    Bit different but usage of two date dimensions

    Comparing-Data-Across-Date-Ranges

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi justivan ,

    If I understand your requirement correctly it seems like all you need to do is use ResDate as the row dimension in the matrix and then alter the measure just a little bit:

    Cumulative Sales = CALCULATE( [Total Pax],
    FILTER( ALLSELECTED( Dates ),
        Dates[Date] <= MAX( Bookings[ResDate] ) ))


    What result are you getting with this? 

    Br,
    J