Need help with some Dates math

AndresSalomon · ‎09-19-2017

Hi all, writing some lines here to obtain your help with a problem that my limited knowledge with DAX is not able to solve.

I have this Table (a summary, of course). My Fiscal Year starts in September. The allocation would be like a percent value, 0 belongs to 0% and 1 belongs to 100% (could have more than 1, like an overallocation).

Asset ID_____________Allocation___________Month____________Overdue date
1_______________________0___________________Sep______________10/27/2017

1_______________________0___________________Oct______________10/27/2017

1_______________________1.1_________________Nov______________10/27/2017

1_______________________1.08________________Dec______________10/27/2017
...

2_______________________0.5_________________Sep______________11/28/2017

2_______________________1___________________Oct______________11/28/2017

2_______________________1.2_________________Nov______________11/28/2017

2_______________________1___________________Dec______________11/28/2017

...

Well the gold rule here is, if the Overdue date is bigger or equal than the date the Asset has allocation (case of Asset #2), the Asset is WRONG; on the other hand if the Overdue date is less than the the date the Asset has allocation (case of Asset #1), the Asset is OK.

So, what I'm looking for is to find a way to give the user to dynamically filter (like a slicer) in a Pivot table the WRONG Assets and the OK Assets. My Pivot table looks like:

Asset ID________Overdue Date_________Sep_______Oct_______Nov_______Dec...

1_________________10/27/2017___________0__________0________1.1_________1.08
2_________________11/28/2017___________0.5________1________1.2__________1
...

The only simple measure I have goes in Values area. Allocation Value:=SUM ( Table [Allocation] ).

What I first need, I thought, is the Start Date of the Asset. I mean, to know if it will start the first days of a month (if it has Allocation close to 1 in that month) or if it will start at mid month (if it has Allocation close to 0.5). So, with this I can compare with the Overdue date. It is important to know if the Asset has a Start Date close to begginings of the month or close to mid month, because the Overdue date can be at any time.
Still don't know how to do it, but is an idea.

Hoping you can help me. Please let me know if I wasn't clear or if you need more details. Really thanks in advance.

Kind regards,

Andy.-

AndresSalomon · ‎09-19-2017

Just a clarification that may helps.

The Start Date of the Asset is the first month that the Asset has an Allocation.

For example, for Asset #1, the Start Date should be 11/01/2017. For Asset #2, 09/15/2017.

So, once we are able to find it, we have to compare ONLY that date with the Overdue date, and then mark the Asset as OK or WRONG.

v-huizhn-msft · ‎09-20-2017

Hi @AndresSalomon,

Actually, you compare the Overdue date and the date the Asset has allocation, where is the date(bold)? From your shared sample table, there is only Overdue date column. Please list the detailed sample table and expected result, you can create similar structure table if your data is confidential.

Best Regards,
Angelia

AndresSalomon · ‎09-21-2017

Hi Angelia, thanks for replying! This problem is squeezing my brain.

The biggest problem I have is actually the one you are mentioning. I don't have a column date that indicate the Asset allocation, I only have the Month column like I explicit there. And from that, as I wrote like an idea, we have to build a column that have the Asset Start Date, and then compare with the Overdue date column and mark all the lines of the Asset as OK or WRONG, so the user can filter with a slicer or something like that.

Do you understand what I mean? Please let me know if I didn't explain well.

Again, thank you very much for replying.

Kind regards,

Andy.-

v-huizhn-msft · ‎09-26-2017

Hi @AndresSalomon,

How to get the date column indicates to Asset allocation? What's the rules of calculation. And you said, "for example, for Asset #1, the Start Date should be 11/01/2017. For Asset #2, 09/15/2017". How did you get the 2017/11/01 and 2017/09/15?

Best Regards,
Angelia

AndresSalomon · ‎09-26-2017

Hi Angelia, thanks for replying and follow up with this problem.

Regarding your question about the "Start Date" column of the Asset, first take a look at the table I have:

Asset ID_____________Allocation___________Month____________Overdue date__________START DATE
1_______________________0___________________Sep______________10/27/2017____________11/01/2017

1_______________________0___________________Oct______________10/27/2017____________11/01/2017

1_______________________1.1_________________Nov______________10/27/2017____________11/01/2017

1_______________________1.08________________Dec______________10/27/2017____________11/01/2017
...

2_______________________0.5_________________Sep______________11/28/2017____________09/15/2017

2_______________________1___________________Oct______________11/28/2017____________09/15/2017

2_______________________1.2_________________Nov______________11/28/2017____________09/15/2017

2_______________________1___________________Dec______________11/28/2017____________09/15/2017

So, that Start Date column in bold there, is the one I need to obtain.

Why? Because with that column I can compare with the Overdue Date column and check if the Asset is OK or Wrong (as the golden rule I posted in my first comment).

How should we obtain it? The Start Date of the Asset is the first month that the Asset has an Allocation and it depends on the Asset allocation; if it is close to 1, the Start Date should be the first day of the month and, if it is close to 0.5 the Start date should be mid month, like 15th.

This is how I'm obtaining the Start Date for Asset 1 and 2. Just check this analysis.

*Asset 1 doesn't have allocation in Sep or Oct, but it has 1.1 in Nov. The allocation is close to 1 and the first month it has is in Nov, so the Start Date is 11/01/2017 (same year of course).

*Asset 2 has 0.5 allocation in Sep. The allocation is close to 0.5 (in fact it is 0.5) and the first month it has is in Sep, so the Start Date is 09/15/2017 (same year of course).

How to achieve this? I have no idea.
I first think in creating an auxiliar Date column based on the Month date column (let say that if it is "Sep" we put 09/30/2017, if it "Oct" we put 10/31/2017, an so on, adding all the days the month has, with a SWITCH maybe), so we can start working with Dates only. Then with that auxiliar Date column we have to check the first month it has allocation as I explain above and create the Start Date column.

But it is just an idea. Maybe there is an easier way to do it.

Hope this clarify my request. Please let me know. Still withouth a solution. Thanks in advance!

Kind regards,

Andy.-