Help Converting Tableau Date logic to PowerBI

[rcarhart2]

I am trying to convert my date logic in tableau to powerBI: I have tried using AI but it does not seem to understand the end goal. Lets see if humans can still beat the AI. I have attached a workbook at the end of this post. I have tried multiple different dax calculations and adding new columns but to no luck. 

For reference I was able to get this logic to work in Tableau and here is a link to a workbook that shows the desired outcomes.
Tableau Date Logic 

High Level overview of logic: A parameter allows the end user to select the date comparison they want and includes a custom where they can select their to and from dates to compare. 
Calculations use the parameter to slice the date calendar for their desired parameter choice. 
PowerBI issues:
Problem 1: make sure AsofDate is using the correct DAX. As of Date should produce a date field.

Problem 2: CurrentDatePeriod should only be true for select dates when selecting the date comparison fields. Should be a Boolean
Problem 3: PriorDatePeriod does not recognize asofdate. Should be a Boolean
Problem 4: DaysinRange Calculations are using incorrect DAX. Helps to calculate the custom fields the end user can choose for their own date comparisons. 
in the visuals you can see where I am validating the logic is working as desired. 
powerBI date logic 
Here is a breakdown of the Tableau logic. 

Parameters:
------------------
From: Data type Date, pulls from date calendar
-----------------

----------------
To: Data type Date, pulls from date calendar
---------------

----------------------
Date comparison
----------------------
Integer: Display as
1: Last Month vs Prior Month
2: Last Quarter vs Prior Quarter
3: Last Month YOY
4: Last Quarter YOY
5: Last Year vs Prior Year
6: MTD vs Prior MTD
7: QTD vs Prior QTD
8: YTD vs Prior YTD
9: Rolling 30 Days
10: Rolling 12 months
11: Custom
12: Last Week YOY

Calculations:

-----------------------
Partial Month Flag: DATETRUNC('month',[Date]) = DATETRUNC('month',{MAX([Date])})
----------------------

-----------------------
Partial Quarter Flag: DATETRUNC('quarter',[Date]) = DATETRUNC('quarter',{MAX([Date]
-----------------------

-----------------------
Partial Week Flag: DATETRUNC('week',[Date]) = DATETRUNC('week',{MAX([Date])})
----------------------

-----------------------
partial year flag: DATETRUNC('year',[Date]) = DATETRUNC('year',{MAX([Date])})
-----------------------

------------------------------
as of date
------------------------------
//MTD (6), QTD (7), YTD (8), Rolling 30 days (9)
IF [Date Comparison] >= 6 AND [Date Comparison] <= 9

THEN {MAX([Date])}

//Custom (12)
ELSEIF [Date Comparison] = 12

THEN [To]

ELSE
{MAX(

//Last Full vs. Prior Month (1) OR Last Full Month YoY (3) OR Rolling 12 Months (10)
IF (( [Date Comparison] = 1 OR [Date Comparison] = 3 OR [Date Comparison] = 10 )
AND NOT [Partial Month Flag] )

//Last Full vs. Prior Quarter (2) OR Last Full Quarter YoY (4)
OR (( [Date Comparison] = 2 OR [Date Comparison] = 4)
AND NOT [Partial Quarter Flag] )

//Last Full vs. Prior Year (5)
OR (( [Date Comparison] = 5)
AND NOT [partial year flag] )
//Last Full week vs Prior Year (11)
OR (( [Date Comparison] = 11)
AND NOT [Partial Week Flag2] )

THEN [Date] END)}

END

-------------------
Current Date Period
-------------------

IF [Date Comparison] = 1
OR [Date Comparison] = 3
OR [Date Comparison] = 6
THEN DATEDIFF('month',[Date],[As of Date]) = 0

ELSEIF [Date Comparison] = 2
OR [Date Comparison] = 4
OR [Date Comparison] = 7
THEN DATEDIFF('quarter',[Date],[As of Date]) = 0

ELSEIF [Date Comparison] = 5
OR [Date Comparison] = 8
THEN DATEDIFF('year',[Date],[As of Date]) = 0

ELSEIF [Date Comparison] = 9
THEN DATEDIFF('day',[Date],[As of Date]) < 30
AND [Date] <= [As of Date]

ELSEIF [Date Comparison] = 10
THEN DATEDIFF('month',[Date],[As of Date]) < 12
AND [Date] <= [As of Date]

ELSEIF [Date Comparison] = 11
THEN [From] <= [Date]
AND [To] >= [Date]

ELSEIF [Date Comparison] = 12
THEN DATEDIFF('week',[Date],[As of Date]) = 0
and [Date] <= [As of Date]

END

-----------------------------
Days in Range
-----------------------------
DATEDIFF('day',
{MIN( IF [Current Date Period] THEN [Date] END)},
{MAX( IF [Current Date Period] THEN [Date] END)})+1
-----------------------------
Days in range comparison
-----------------------------
DATEDIFF('day',
{MAX( IF [Prior Date Period] THEN [Date] END)},
{MAX( IF [Current Date Period] THEN [Date] END)})

-----------------------
prior date period
----------------------
IF [Date Comparison] = 1 THEN DATEDIFF('month',[Date], [As of Date]) = 1

ELSEIF [Date Comparison] = 2 THEN DATEDIFF('quarter',[Date], [As of Date]) = 1

ELSEIF [Date Comparison] = 3 THEN DATEDIFF('month',[Date], [As of Date]) = 12

ELSEIF [Date Comparison] = 4 THEN DATEDIFF('quarter',[Date],[As of Date]) = 4

ELSEIF [Date Comparison] = 5 THEN DATEDIFF('year',[Date],[As of Date]) = 1

ELSEIF [Date Comparison] = 6 THEN DATEDIFF('month',[Date] ,[As of Date]) = 1 AND [Date] <= DATEADD('month',-1,[As of Date])

ELSEIF [Date Comparison] = 7 THEN DATEDIFF('quarter',[Date],[As of Date]) = 1 AND [Date] <= DATEADD('quarter',-1,[As of Date])

ELSEIF [Date Comparison] = 8 THEN DATEDIFF('year',[Date],[As of Date]) = 1 AND [Date] <= DATEADD('year',-1,[As of Date])

ELSEIF [Date Comparison] = 9 THEN DATEDIFF('day',[Date],[As of Date]) >= 30 AND DATEDIFF('day',[Date],[As of Date]) < 60

ELSEIF [Date Comparison] = 10 THEN DATEDIFF('month',[Date],[As of Date]) >= 12 AND DATEDIFF('month',[Date],[As of Date]) < 24

ELSEIF [Date Comparison] = 11 then DATEDIFF('week',[Date],[As of Date]) = 52

ELSEIF [Date Comparison] = 12 THEN DATEDIFF('day',[Date],[As of Date]) >= [Days in Range] AND DATEDIFF('day',[Date],[As of Date]) < [Days in Range]*2

END

PowerBI Date Logic 
Any help or feedback is greatly appreciated. Feel free to ping me with any questions. Thank you in advance. If anyone can help with this I will be truly convinced PowerBI can do what tableau can.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. Can you describe the business problem you are trying to solve?

What have you tried and where are you stuck?

[rcarhart2]

Hey @lbendlin thank you for checking out my post. Did you have a chance to view my tableau report. I have a demo of all the functionality of how it works in tableau.
The business function I am trying to solve for it to create a dynamic set of calender dates that the end user can filter on. For example, if they select  last month vs prior month all the underlying metrics in the visual change. This is the base for the rest of the data model. After I solve this i will bring in my fact and other dimension tables. 
Where I am stuck. 
1. First I want to make sure my AsOfDate calculation is using the right DAX. right now it looks like it is. When you open the PowerBI report I attached you can see when you select a date it will change the asofDate:

2. The next calculation is the CurrentDatePeriod. This does not seem to be working correctly. When I select on one of my date filters, such as last month vs prior month, the current date period should create a boolean where only the currentdateperiod is true. It is not correctly doing this. 
see  picture.

Do you see how anything after the 2nd is also true. This should be false since that is not the current date period(last Month). Also anything prior to last month should also be false. 
3. In my prior date period calculation I want to dateadd my AsofDate field but it does not seem to recognize my asofDate. 
4. my daysinrange calculation throws this error: a function 'PLACEHOLDER' has been used in a true/false expression that is used as a table filter expression. this is not allowed. 
5. And lastly, In my calculations you will see i commented out some of the code. This is my To and From Parameter. Pretty much if the end user selects custom they then choose their to date and their from date and the calculations will use that date range and then the prior date period will be that same time frame from last year. 

As far as what I have tried. I have spent multiple days working on this, asking different AI's and trying all different types of dax calculations. It is way to long to list everything which is why I decided to see if someone could look at my tableau logic and see how they would do it in dax. 

Check out the tableau report because it shows all the functionality in work. Maybe there is a totally different way to approach this in PowerBi that I am not thinking about. 

Appreciate your help.Thank You!

[lbendlin]

The standard approach in Power BI is to use the Filter Pane, and to train your report users on how to use it.

If you don't like that, use a disconnected reference table and a giant SWITCH statement to cover all scenarios.

[rcarhart2]

I am trying to use a giant switch statement. I know how to use the filter panes but I want more advanced filtering abilities. 

[lbendlin]

Please fix the sample data link. Asks for access.

[rcarhart2]

@lbendlin it should be fixed now. 

[lbendlin · Accepted solution]

Your calendar table only extends into the past.  Time intelligence functions require a covering table, including the results of the calculation. 

You are missing a Year-Quarter column

Your selections are a mix of comparisons ("versus") and scalar values ("YOY").  That is not something you can meaningfully show in a single visual.

Here's a general approach for the Switch statement, using measures as visual filters:

[rcarhart2]

@lbendlin thanks for taking time out of your day to look at this. This gives me a different way to look at the problem. appreciate it.