Forum Discussion

Clout's avatar
Clout
Icon for Helper III rankHelper III
5 years ago

Calculate within one DAX Formula with multiple values for a specific year

Hello guys, I have one problem with a dax formula

 

I have these tables:

 

 

This table is simplified, every year has data for every day from the 1.1 till the 31.12

 

"Flows"

Date        /

1.4.2019  / 

1.3.2020  /

6.3.2021  /

 

"Date Table"

Date       /  Year

1.4.2019 / 2019

1.3.2020 / 2020

6.3.2021 / 2021

 

"Load"

Year     /  Volume

2019   /  453.453

2020  /   245.646

2021  /  321.988

 

In my data model, the date of "Flow" is connected with the date of "Data table". And the table "Load" is connected with Year in the "Data table".

 

Now I want to make a calculated column in the table "Flows" something like that:

JSM_d = DIVIDE(Load[Volume] , (COUNTROWS(Flows) * 86.4))
 
So I want to make the calculation for the specific year within the date.
When I calculate JSM_d for the year 2019, I want to use the value from the table "Load" which is next to the year and then I want to count the total rows/days of this specific year for the division. And so on.
 
Is this possible?
 
Thanks in advance!
 
 
 
 

 

 

 

 

 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Clout , Create a date in load table like

    date = date([year],1,1)

     

    or

     

    date = date([year],12,31)

     

    Join that with date table.

     

    Refer my blog how this end date will help in getting value

    Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD:

    • Clout's avatar
      Clout
      Icon for Helper III rankHelper III
      5 years ago

      Hello amitchandak thank you for your help,

      but I dont get it how to visit your blog trough the dashboard. Can you give me the direct link pls?

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Can you please elaborate on what you are trying to achieve or can you post sample data and expected result so it is clear on what needs to be implemented?