Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate SUM between two datetimes

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

     

AlB's avatar
AlB
Community Champion
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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