Week-ending Date Running Total Quandry

[Anonymous]

Hi All

I'm new to Power Bi - coming from a SQL and Tableau background, I am finding it tricky! So, my problem is I have a table that reports by week-ending date - so there are no dates between. The table records submissions made to my organisation by other organisations and the report is trying to identify (for a pie chart) who is submitting regulary, who is submitting intermittently and who is not submitting at all over a period of six weeks (or in this case, the last six week-ends). So, this seems like it should be simple enough to get the data to formulate mny three tranches with the following formula in a DAX Calculate function where 'SubmissionDetails'[Total Submissions] has the number of submissions made that week and 'Submission Details'[reportDate] is the week-ending date:

CALCULATE(
sum('SubmissionDetails'[Total Submissions]),
DATESINPERIOD('Submission Details'[reportDate],LASTDATE('Submission Details'[reportDate]),-42,DAY)
)

 However, all this does is return the number of submissions for the current week i.e. it's exaclty the same as the 'SubmissionDetails'[Total Submissions] column. I'm stumped to be honest, the Submission Details'[reportDate] is most definitely a date column. I do have a date dimension, but when using the formula above, it doesn't like me using a combination of the Submission Details'[reportDate] and the date dimension date field in the DATESINPERIOD formula. I would love some help to try understand if you can reference two tables in this formula, or there is something I am missing! Thanks in advance!

[Whitewater100]

Hi AJCT:

There is some set-up required, like adding in calculated columns in the Date Table, etc. I have a possible solution on the attached link. I used dummy data for companies A-D and randomly put in report submission dates.

I hope you can mark as solution if this is correct for your needs.

https://drive.google.com/file/d/1Zsssme9lrP_HHpoTjDd__TgmbhK-C5tL/view?usp=sharing 

[daXtreme]

Before using a DAX function, you have to know its exact meaning and its exact requirements. Here's the place where you learn such things: https://dax.guide/datesinperiod 

Take a good look at the Notes section...

[Whitewater100]

Hi:

You need a date table. I'll paste an example below. It needs to be marked as date table with one to many relationship with the fact table. Time Intel calculations do not work unless this is done. You would form relationship to Date filed in Dates table and WeekEnd field in your fact table. I will paste DAX date table code (MODELING>NEW TABLE> 

Dates = ADDCOLUMNS ( CALENDAR (FIRSTDATE(Sheet1[ Date]), TODAY()), "year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), "year-month", FORMAT ( [Date], "YYYY-MM" ), "month-year", FORMAT ( [Date], "MM-'YY" ) )

I will also paste a basic model below with dim table above to reinforce those filter the fact table.