Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Converting monthly value into daily DAX

hi everyone!

 

please help with the following,

 

I'm trying to convert montly target value for sales into daily average with respect to number of days in a given month.

 

this is my data model

 tables are connected through calendar table, 
Actual table has data on daily level, but Plan table has data on monthly level

 and I need to see it in the following format

important thing to concider is days in each month

DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 
    Please try

    Planned Sales Daily =
DIVIDE (
    [Planned Sales],
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Calendar'[Date] ),
            REMOVEFILTERS (),
            VALUES ( 'Calendar'[Year] ),
            VALUES ( 'Calendar'[Month] )
        )
    )
)

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous 

    why did you divide the march planned amount over 46.5 days? 331,962/46.5 = 7,139?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      hi tamerj1 ,
      sorry, my bad I captured more days for march,
      daily figure should be 10 708

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Anonymous 
        Please try

        Planned Sales Daily =
DIVIDE (
    [Planned Sales],
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Calendar'[Date] ),
            REMOVEFILTERS (),
            VALUES ( 'Calendar'[Year] ),
            VALUES ( 'Calendar'[Month] )
        )
    )
)