Gave a shot at this. Below is what I came up with. I substituted today() with a parameter as I did not have actual/current year data to display. You may replace parameter with Today() in your case.
To achieve the output, I created only two additional calculated fields
1) Dynamic Date Filter (Added this to filter shelf and selected 1)
IF [Order Date]=[Select Date]-2 OR [Order Date]=[Select Date]-30 THEN 1
2) Sales by Dynamic Date
IF [Order Date]=[Select Date]-2 THEN [Sales]
ELSEIF [Order Date]=[Select Date]-30 THEN [Sales]
You may extend this logic to exactly suit your requirements if it looks relevant to your ask.
I made a few mods to your worksheet and I think you can get the layout you desire. A brief desc of what I did.
1. Created a date header dimension using your calculated field logic.
IF [Calc_yesterday's Data] > 0 THEN [Date Day Before Yesterday]
ELSEIF [Calc_Same Day Last Month Data] > 0 THEN [Calc_Same Day Last Month] END
2. Created a single measure to work with the date header field.
IF [Kpi Key] = 1 THEN [Amount] ELSE [Count] END
3. Drag DateHeader field to filters and exclude nulls.
4. Layout per screenshot.
Let me know if you have any questions.
PS. I added data to your count field which is why you no longer see zeros
Sample Date Mod.twbx 25.3 KB
Thank You so much. I shall try both the solutions and update what worked
Thanks Ivan..It worked for these 2 measures however if i need to do variance and % variance from these 2 measures then this formula doesn't work..is that correct ?
eg: Variance:- Sum([Calc_Yesterdays_Data]) - Sum([Calc_Same Day Last Month Data])
% Variance :- [Calc_Variance]/Sum([Calc_Same Day Last Month Data])
Just curious to know if you tried the option I gave.
I was not able to open your workbook as I was running on a lower Tableau version and hence mocked up on sample data.
Was my understanding of your requirement wrong?
And @Ivan Young - Interesting solution. Thanks