Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
3 years ago

Month Number

I have a Date table with a column called 'Month No' where April = 1 and March is 12

 

I have a Measure that calculates New Cases YTD = 

 

YTD New Cases = CALCULATE(
    TOTALYTD(DISTINCTCOUNT('Cases'[incidentid]),'Cases'[createdon],"31/03"))

 

I would like to DIVIDE this measure by the current month number which is 12

 

I know its probably a simple thing to do but I'm going around in circles trying to work this one out, can anyone help please?

Thanks

Need Help
Show and Tell

2 Replies

  • bsheffer's avatar
    bsheffer
    Continued Contributor
    3 years ago

    divide([YTD New Cases], max('date'[Month No])) doesn't work?

     

    dynamically

    divide([YTD New Cases],

    calculate(max('date'[Month No]),

    'date'[date] = today()  -- or month('date'[date]) = month(today()) if you don't update daily

    ))

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      3 years ago

      Thanks for this, I'm not getting 35 instead of 32.5 (My YTD figure is 390 so 390/12 = 32.5 per month)

       

       

      Current Month = MAX('Date'[FY Month No])

       

       

      The code above gives me month 12, the problem I have is what happens next month (April) where the Month No reverts to 1 again?

       

      I also have a Calendar month Column where Jan = 1 and Dec = 12

       

       

      Month No = Month([date])

       

       

      and a FY Column where Apr will become 2023/24

       

       

       

      FY = IF([Month No]<=3,"FY"&[year]-1&"-"&[Year],"FY"&[year]&"-"&[Year]+1)

       

       

       

      Is there a way by using all of this code to calculate the current month number so 12 (this month) will become 1 next month (Apr and the start of the new financial year)?

       

      Thanks