DatePicker sales between with DAX

[luisccmm]

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.

Thanks in advance!!!

[parry2k]

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

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

[luisccmm]

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]

@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.⚡

[Syndicate_Admin · 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

[luisccmm]

@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