Calculate First Day of month for different countries

Hi,

I am still busy with enhancing the model that I have shown you before. The following:
In all of the tables being used there are two columns: Time_period_start and Time_period_end.
There are measures that are calculating the first date when the data appears and a measure for the last date the data appears.

These dates are based on country level and a franchises within that countries.

When I want to do this in SQL server it would be something like a group by function on the countries and franchises.

Question : When I remove both columns for time_period_strat and time_period_end. I add a column with the dateKey and having a measure table for calculating the first and the last day per country and franchise.

Latest Sales Date =

DATE(LEFT(MIN(FACT_SALES_MONTHLY[FK_DATE_ID]),4),MID(MIN(FACT_SALES_MONTHLY[FK_DATE_ID]),5,2),RIGHT(MIN(FACT_SALES_MONTHLY[FK_DATE_ID]),2))

When I have this measure it needs to be based on country and franchise. I need some kind of filter ?
Can you point me in the right direction please.

kind regards,
Sjaak

Probably need some images here and more detail to provide better assistance. It’s difficult to understand everything at play here without seeing what it looks like and then the output you ultimately need.

Thanks

Ok Sam,

Some extra images here:

Having a table for key measures
image

For the records where the date needs to be calculated have only end of month date

And finally having my expression for the calculation:

Latest Sales Date = DATE(LEFT(MIN(FACT_SALES_MONTHLY[FK_DATE_ID]),4),MID(MIN(FACT_SALES_MONTHLY[FK_DATE_ID]),5,2),RIGHT(MIN(FACT_SALES_MONTHLY[FK_DATE_ID]),2))

What I want is to have the first and last date when records are loaded. For country and the franchise. Seeing there are only end dates stored , calculate the beginning of that month also

Hopefully this clarifies it for you Sam…

Firstly really attempt to format your formula better. It’s difficult to read them presented like that are.

Here’s some tips

Are you sure those are actual measures?? They look like calculated columns to me

A measure would look like the below.

image

I’m still not 100% sure on the exact scenario here as I don’t feel I am seeing all the information required to get it all.

All of this logic should be happening mostly with the date table. Have you got this into your model? This is very important and would likely make this quite easy to solve in my opinion.

Also I will likely need to see what the model looks like as well.

When you say first and last date when records are recorded, is this information actually somewhere in your tables that you already have and you’re attempting to extract this information?

Will probably need a demo download of the scenario to really help I think from here. I just don’t believe I have all the information I need to assist further at this point.

Thanks

Hi Sam,

Without providing extra pictures maybe I explain it differently… There are 5 tables having two data fields. Time_period_Start and Time_Period_End. The data is loaded monthly where actually the data is snapshots per month.

Going to a dimensional model seems logical to just have one datekey in this tables. Then there is a choice do you take the first day of the month or the last day.

There is a overview where the min and max date are picked to indicate first and last records of the data.

So when there is only one datekey I want to derive / calculate the first or last day of that month.

Hopefully this is a better explanation

kind regards,
Sjaak

Sjaak,

Just to echo what Sam said on formatting formulas/measures. His video is great but this website provided by Alberto & Marco (The Italians) is very useful.

Guy

Enterprise%20DNA%20Expert%20-%20Small

Hi Guy,

Thanks for your reply.

And stupied of me to try it doing difficult. In the original model there are 5 tables mentioning the tme_period_strat and time_period_end. In order for me to make the model efficient as possible and creating a star-schema. I am reducing the columns in all of the tables. Having a date dimension and having only one date in the tables referring to the date dimension.

I was stupied to get the last day of the month and trying to calculate the first day of the month. There are functions for EOFMONTH so then I take the first day of the month as a measure and calculate the MAX date with EOFMONTH . Easy peasy.

Good suggestion for the formatting.

Kind regards,
Sjaak