Calculate SUM between two datetimes

[Anonymous]

Hello Everybody!

I need your input on an approach of mine.

I have following table via direct query from a SQL database:

The table is conneced to a simple date table, lowest granularity is day. Building a measure and summing up the value for "Nettowert" for a day (time 0-0) and filtering by attribute "KZ2"is easy.

The hard part is summing up the value for times between 6 and 6.

In order to reign in the granularity issue, I build a time table (shift table) with the same granularity (minutes) as my value table.  I also calculated a date shift, meaning the time between midnight and 6 o clock in the morning is the same day as the previous day:

Now I am stuck.

How do I build my measure summing up the values using the new date values I created with my shift table?

I want to be able, to build visuals and slicers using my "normal" date table. Maybe virtual relationships...?

Could there have been an easier way to deal with the granularity issues? 

Best Regards

Rainy

[AlB]

@Anonymous

Ok, with the realtionship you have between Zeitstempel start  and Zeitstempel it should work. 

Place Datum Schiechbasierte Berechnung in the x-axis of a chart for instance and then a measure like

           Measure = SUM( Table1[Nettowert]) )

should give you the sum between 6-6 for each date

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

That works but is unfortunately not what I need to do.

I need to use this measure for other calculations with data from different fact table which are connected to my main date table. Also in this scenario I can´t use my main date table as a slicer.

Cheers

Rainy

[AlB]

Hi @Anonymous 

I'm not sure I follow. Can you not simply create a relationship (active or inactive to activated through USERELATIONSHIP) between your date table and the new date column you've created (Datum Schiechbasierte Berechnung)??

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous · Accepted solution]

USERELATIONSHIP did the trick, I had to do some minor changes to my data model and it works now.

Thanks a lot for the right prompt.

My solution looks like this:

FM SYS1 6-6 =

CALCULATE(CALCULATE(SUMX('FM geliefert','FM geliefert'[Nettowert]),

FILTER('FM geliefert',

'FM geliefert'[KZ2]="A" ||

'FM geliefert'[KZ2]="B" ||

'FM geliefert'[KZ2]="C")),

USERELATIONSHIP('01 Kalender'[Datum],'05 Schichtzeit Kalendar'[Datum Shift]))

Cheers Rainy

[Anonymous]

Hey AIB!

I think my issue right now is not understanding relationships properly.

My data models so far had only direct relationships from one date table to my fact table(s). Now I have another time table and I am not sure how to proceed.

My data model looks like this now:

Calculating my values per day (time range 0-0) with the direct relationship - done.

Now I want to calculate the value based on the date "date shift" (time range 6-6) and also use this measure in visuals with my regular date field from my date table.

I am getting lost in all the possibilities: Userelationships, Treatas, Filter, physical relationsships in the data model,...

Maybe the solution is simple, but I am not able to see it at the moment.

Best Regards

Rainy