Using time intelligence within my fact table

[BIlix]

Hello community,

I encounter some problems with my fact table and its source data.

In my Fact table are monthly budget and cost values for different projects.

Project A  November 2023 500, December 2023 100 

...

The source data are excel exports which are created on demand and are appended to the fact table. 

From the file name I create a column for the date of the file export, refresh date.

Calculations like sameperiodlastyear should be included in my report. 

Currently there are multiple and repeating entries for every month, for example when a file export is done twice in a month. The monthly values can differ in the timeframe of a month when data gets updated. 

Is there any possibility to use sameperiodlastyear or similiar code with this data model when I want to compare todays data with the data from previous year of the same timeframe?

[BIlix]

As you can see, in this example the Actual Value of Project 1 in February changed between the two export dates. Data of a month can sometimes be updated in later months, when invoices are delayed. 

The table is connected Many to one to the date table via the Date column

[lukiz84]

can you visualize the model?

[BIlix]

I already got a date table.

But lets say my fact table data consists of 20 exports during the year. Therefore I have several values for the same month, for example January 2023. For 2022 its the same. I need to assure that the data from previous year is from the same refresh date as the data from my recent export. I hope the issue is understandable

[lukiz84]

Not really a powerquery question.

But yes, why not?

Just create a datetable which has the min and max date from your exports (can be generated during refresh) and connect it to the date column in your "source" data.