Forum Discussion

MightyMicrobe's avatar
MightyMicrobe
Icon for Helper II rankHelper II
6 years ago
Solved

Queries with overlapping date periods

Hi all,  I am exporting data out of Adobe Analytics and need to build a view showing metrics over a 45-day period, shifted weekly. Essentially, I need to see week on week change of the overlapping/p...
Need Help
Tips and Tricks
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi MightyMicrobe 

     

    Your data is too scarce to repro the full issue. for the "need to build a view showing metrics over a 45-day period, shifted weekly" you can probably create a calculated table first then add the measure:

     

    date = var k = ADDCOLUMNS(FILTER(CALENDAR(DATE(2019,01,01),DATE(2020,01,08)),WEEKDAY([Date],2)=3),"date end",[Date]+44)
return
k
    Weeks back = CALCULATE(COUNTROWS('date'),FILTER('date',[Date]>=EARLIER([Date])))-1

     

    I can't figure out where the metrics info from, but you can manage the calendar table with your data to calculate them automatically. 

    Pbix attached.

     

MightyMicrobe's avatar
MightyMicrobe
Icon for Helper II rankHelper II
6 years ago

No, this will not work, unfortunately. The nature of the data I'm working with is such that 45 day period != sum total of 45 individual days, I need to ingest the data as one "block" of 45 days, not as 45 individual blocks of 1 day each. 

 

Not sure if it makes sense but that's about the best way I can explain it 🙂