Filtering formula by various dates

Used for anything related to the new Power BI Desktop and Service tools
PhilC
Posts: 157
Joined: Tue Sep 09, 2014 8:13 am

Filtering formula by various dates

Postby PhilC » Wed Oct 24, 2018 1:42 pm

Hi,

I am looking to produce a visual that shows Funding over three years (slicer selected year and next two) for two different measures, in a stacked column chart.

I have attached a PIBX and Excel files with example data, and my attempt at the two formulas. I have hard coded the VARs but I need them to be dynamic based on the slicer selected year.

There are two dates involved in data:
Start Date - the start of the project
Funding Year - the year the income happens for the project

Therefore there can be multiple line per project, based on the [Funding Year], however the [Start Date] is the same for each row for a particular project.

Funding Amount Current Year - shows total of Amount where the [Start Date] is in the slicer selected year, and where the [Funding Year] is in the slicer selected year or the next two years. ie Projects with a [Start Date] in 2018 with [Funding Year] in 2018, 2019, 2020.

Funding Amount From Prior Years - shows total of Amount where the [Start Date] is prior to the slicer selected year, and where the [Funding Year] is in the slicer selected year or the next two years. ie Projects with a [Start Date] prior to 2018 with [Funding Year] in 2018, 2019, 2020.

Thanks in advance,
Phil
Attachments
Example Calculations.xlsx
Excel file showing expected results
(10.35 KiB) Downloaded 6 times
DAX Help Example.pbix
PBIX file with data and attempt at measures
(343.36 KiB) Downloaded 5 times

RamanaV
Posts: 22
Joined: Thu Oct 19, 2017 12:57 pm

Re: Filtering formula by various dates

Postby RamanaV » Wed Oct 24, 2018 8:34 pm

Hi,

I think it may work this way.

Here are the two formulas with a Slicer selection of a Year.

Funding Amount Current Year - Alternative =
VAR FirstYear = SELECTEDVALUE('Calendar'[Year])
VAR FinalYear = FirstYear + 3
VAR FirstYearDate = DATE(FirstYear, 1, 1)
VAR FinalYearDate = DATE(FinalYear, 12, 31)
RETURN
CALCULATE (
[Total Income],
ALL ( IncomeAOU[FUNDING YEAR], IncomeAOU[START DATE]), ALL('Calendar'),
FILTER(IncomeAOU, IncomeAOU[FUNDING YEAR] >= FirstYearDate && IncomeAOU[FUNDING YEAR] <= FinalYearDate && YEAR(IncomeAOU[START DATE]) = FirstYear)
)

Funding Amount From Prior Years - Alternative =
VAR FirstYear = SELECTEDVALUE('Calendar'[Year])
VAR FinalYear = FirstYear + 3
VAR FirstYearDate = DATE(FirstYear, 1, 1)
VAR FinalYearDate = DATE(FinalYear, 12, 31)
RETURN
CALCULATE (
[Total Income],
ALL ( IncomeAOU[FUNDING YEAR], IncomeAOU[START DATE]), ALL('Calendar'),
FILTER(IncomeAOU, IncomeAOU[FUNDING YEAR] >= FirstYearDate && IncomeAOU[FUNDING YEAR] <= FinalYearDate && IncomeAOU[START DATE] < FirstYearDate)
)

Hope this helps.

Ramana V

PhilC
Posts: 157
Joined: Tue Sep 09, 2014 8:13 am

Re: Filtering formula by various dates

Postby PhilC » Wed Oct 24, 2018 10:28 pm

Thanks for the suggestion.

It seems like it should work based on what you provided, but the visual is showing results for more than the selected year plus two more (I did change the + 3 to +2).

I tried hard coding the FirstYear value to 2018 in both measures and the result is as expected, so just need to solve that aspect for this to be dynamic with slicer selection.

What change is needed to get the correct value into FirstYear based on the slicer?

Cheers
Phil

RamanaV
Posts: 22
Joined: Thu Oct 19, 2017 12:57 pm

Re: Filtering formula by various dates

Postby RamanaV » Thu Oct 25, 2018 9:32 am

Hi,

Yes, it should be +2 and not +3.

To get the correct value into FirstYear based on the slicer, you need to use DAX SELECTEDVALUE function.

If it is not working by using SELECTEDVALUE directly, try to use What-if feature in Power BI Desktop.

Matt has explained the feature with an example in an easy to understand way in his Power BI book (Page 139).

I will try to solve this in the weekend if there is no hurry.

Or you can give a try meanwhile.

Regards
Ramana V

PhilC
Posts: 157
Joined: Tue Sep 09, 2014 8:13 am

Re: Filtering formula by various dates

Postby PhilC » Fri Oct 26, 2018 9:09 pm

Thanks for having a look.

I cannot understand why the measure works when the FirstYear is hard coded to 2018 but not when I use the SelectedValue.

The attached PBIX has the example with your formula. It is not producing the correct result. If you change Funding Amount Current Year measure to be hard coded to 2018, the correct answer is there.

I have the [Selection Slicer] measure to harvest the slicer value and can test with the Card visual, it seems to be working ok.

Any thoughts will be appreciated.

Cheers
Phil
Attachments
DAX Help Example.pbix
(347.3 KiB) Downloaded 6 times

RamanaV
Posts: 22
Joined: Thu Oct 19, 2017 12:57 pm

Re: Filtering formula by various dates

Postby RamanaV » Sat Oct 27, 2018 8:45 pm

Hi,

Actually you are almost near the the solution.

The error was DATE function is expecting a whole number for year, whereas 'Calendar'[Year] is of datatime data type.

I have done the following:
Created a table with a column of whole numbers (2002 - 2024).
I changed the slicer to have this column.
I have written a measure SelectedYear for the selected value from the slicer. I have turned off multiple section for the slicer.
I modified the formulas to have SelectedYear.

So now some values are getting displayed.

Please check the modified version if the values are correct.

DAX Help Example Modified.pbix
(346.54 KiB) Downloaded 5 times


Best wishes
Ramana

PhilC
Posts: 157
Joined: Tue Sep 09, 2014 8:13 am

Re: Filtering formula by various dates

Postby PhilC » Sat Oct 27, 2018 8:59 pm

Hmm, interesting. Thanks for investigating, will have a look at the file you posted tomorrow night or Monday.

Not super keen on having a different field to use as the slicer, as am using this slicer linked across multiple pages and want it to be coming off the date table as it affects other calcs in other places.

Cheers
Phil

RamanaV
Posts: 22
Joined: Thu Oct 19, 2017 12:57 pm

Re: Filtering formula by various dates

Postby RamanaV » Sun Oct 28, 2018 9:49 am

Hi,

I suggest the following.

First please check if the numeric slicer is giving correct results on selection of different Year values.
Then the issue - it works when Year is hard-coded to 2018 and otherwise not - will be resolved.

Next check your formulas, to see where a datetime is expected and where a numeral is expected.
Then check if an appropriate data type conversion works.

Then you can safely use it across the pages.
Hope it works.

Best wishes
Ramana

RamanaV
Posts: 22
Joined: Thu Oct 19, 2017 12:57 pm

Re: Filtering formula by various dates

Postby RamanaV » Wed Oct 31, 2018 8:51 am

Hi, Is this resolved?

PhilC
Posts: 157
Joined: Tue Sep 09, 2014 8:13 am

Re: Filtering formula by various dates

Postby PhilC » Sun Nov 04, 2018 9:50 pm

Hi, thanks for following up. Still do not have it working with the slicer based on the Year column of the Calendar table. Hard coded it is fine, using the Calendar[Year] produces the correct result when I use a Card visual to test, so still stumped. Don't think the DateTime aspect mentioned is relavant.

Have come across two blog posts (see below) - the first one uses the same Date[Year] column, but the second one has a comment that the slicer does not work if the column used is in a related table, but I don't fully understand why. Same question was asked in the comments but not answered.

https://www.kasperonbi.com/dax-selectedvalue-function/

Uses Date[Year] field in example, but not in terms of filtering the data.

http://radacad.com/dynamic-date-range-from-slicer#comments

The challenge is if the “Month” slicer uses a field from the data table used to generate values for the visuals it will restrict the rows from the data table available to generate the axis, that no amount of fiddling with row or filter context can over-ride. The same is true when using a field for the slicer from a related table.

A way to solve this is to use a field for the slicer from a table that has no relationship with the data table.


Cheers
Phil


Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 2 guests