cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luisccmm
Helper II
Helper II

DatePicker sales between with DAX

Hi,

 

I have made the attached PowerBI app where I have the following:
Powerbi file 

- DatePicket set to select MAX date only.

- Calendar Table.

- Contracts table (Date, Contractnumber columns).

 

I am looking to build a formula that if I Select the MAX date from DatePicker it will show Counting Contracts from First date on this year till the selected date.

 

To do this i have build three functions:

last_date = LASTDATE('Calendar'[Date])
first_date = date(year(LASTDATE('Calendar'[Date])),1,1)

dates_in_period = 
 CALCULATE(count(Contracts[Contract_id]),
 FILTER('Calendar','Calendar'[Date]>=[first_date]&&'Calendar'[Date]<=[last_date]))

 

The problem here is that if I drop this measure with Month Dimension:

still there is values in months when they should be set to 0 or null.

 

As in the image below where I have set  21/04/2020 and  there are values from this date when I want to see null or =0.

date_contracts.png

 

Thanks in advance!!!

1 ACCEPTED SOLUTION

@parry2k Fourtanately after searching on the web I have found a solution for this problem in this article. The solution comes from creating another Calendar Table without any connection with the Data Model, and use this tables as Date Slicer, so even when is selected no filter is applied to the KPI. Then use variables as data imputs in measure that come from the Isolated Calendar Table ( in the slicer).

https://www.sqlservercentral.com/articles/how-to-override-date-filters-in-power-bi

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@luisccmm you are looking at the data in the wrong way, in the date selection you have a date from 2010 and you have data for 2019, if you looking it by month, it will show you 2019 months as well. this is how it suppose to work.

 

image.png

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k   I know what is on the Date Filter panel.

 

But still have you seen the Formula I have wrote in the Post carefully, ¿How is that I am limited time period from 1st Jannuary 2020 till max calendar Date and I am getting values before that interval?.

 

I am trying to achieve this and seems to me is well planned but the result is not as expected.

last_date = LASTDATE('Calendar'[Date])
first_date = date(year(LASTDATE('Calendar'[Date])),1,1)

dates_in_period = 
 CALCULATE(count(Contracts[Contract_id]),
 FILTER('Calendar','Calendar'[Date]>=[first_date]&&'Calendar'[Date]<=[last_date]))
parry2k
Super User
Super User

@luisccmm why not use DATESYTD function

 

Contract Count YTD = 
 CALCULATE(
count(Contracts[Contract_id]),
DATESYTD ( 'Calendar'[Date] )
)

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k Fourtanately after searching on the web I have found a solution for this problem in this article. The solution comes from creating another Calendar Table without any connection with the Data Model, and use this tables as Date Slicer, so even when is selected no filter is applied to the KPI. Then use variables as data imputs in measure that come from the Isolated Calendar Table ( in the slicer).

https://www.sqlservercentral.com/articles/how-to-override-date-filters-in-power-bi

@parry2k Still seeing values in following months when i use DATESYTD

APP is here if someone want to try.

https://drive.google.com/file/d/1Dm2qU2lkJMsGgtJmdQ3nbZ-UXk-Jq388/view?usp=sharing

 

data_contract 2.png

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors