Filtering by the TAIL! RRS feed

  • Pregunta

  • Hi guys, I've been trying to workout and MDX for an SSRS dataset but I cannot achieve the final result I need.

    Imagine the following scenario:


    I want to measure the Marketing Campaigns that are expiring the next week per State (the same campaign can finish in different dates depending on the state), in order to extend them. The problem is, my SSAS cube does not have a date dimension with the "Date Expiration". Instead, there is a "Date Active Campaign" that will have each of the days since the campaign start until the campaign ends, therefore the relationship between "Date Active Campaign" and my fact table is 1 to N.

    I would like to have an MDX that:

    Part 1- Calculates what the last "Date Active Campaign" is for each campaign and state,

    Part 2 - and then filter all those campaigns that have the last "Date Active Campaign" in the next week.

    SOLUTION: Imagine that next week is Week 12, the query should return:

    The idea is to filter the query by Week 12... answering the question: "Give me all the Campaigns by State that have a Last Date in the Week 12"

    Any proposals?

    Many thanks for you help!

    • Editado mts_aa lunes, 22 de agosto de 2016 14:20
    lunes, 22 de agosto de 2016 10:22