Forum Discussion

ebrownretail's avatar
ebrownretail
Resolver I
2 years ago
Solved

Calculate WOS based on current month

Hi!

I am trying to calculate the WOS for my items by store based on the prior week sales. 

 

I have the prior week sales already as a measure and it works. I am running into an issue when i try to multiply by what the current WOS number is. 

 

Curr WOS =
CALCULATE([Prior Week Sales] * CALCULATE(MAX('WOS Listing'[WOS])),FILTER(Dates,Dates[Month Name] = TODAY()))
 
I have a table that lists what WOS we should use by month, and i want it to be able to find that number based on what the current month is so i dont have to change it all the time. I also have a dates table.
 
WOS Listing looks like this:

 

Any help would be greatly appreciated on what i am doing wrong!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from miTutorials and SachinNandanwar , please allow me to provide another insight:


    Hi, ebrownretail .

    Regarding the issue you raised, my solution is as follows:

    You can try using the Format() function to convert the date type to the text type.

    1.First I have created the following table and the column names and data are the data you have given:
     

     


    2. Below are the measure I've created for your needs:

    Measure = MAX('Table'[Month])=FORMAT(MAX('Table 2'[Date]),"mmmm")
    Measure 2 = FORMAT(MAX('Table 2'[Date]),"mmmm")

    3.Here's my final result, which I hope meets your requirements.
     

     

     

    4.Here is the relevant documentation:
    FORMAT function (DAX) - DAX | Microsoft Learn

    Of course, if you have any new ideas, you are welcome to contact us.

    Please find the attached pbix relevant to the case.
     

    Best Regards,

    Leroy Lu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


     

4 Replies

  • miTutorials's avatar
    miTutorials
    Super User
    2 years ago

    FILTER(Dates, Dates[Month Name] = TODAY()) is problematic because Dates[Month Name] is a text column representing the month name (e.g., "July"), while TODAY() returns a date. These cannot be directly compared and you will have to change this!

  • ebrownretail's avatar
    ebrownretail
    Resolver I
    2 years ago

    thank you for the information. How do i get it to reference today to find out what the month name is?

    • SachinNandanwar's avatar
      SachinNandanwar
      Impactful Individual
      2 years ago

      Allow me to respond.

      I am not to sure if there is any inbuilt function to identify month name.You would have to write a custom measure to indetify the name based on MONTH(TODAY()) which a returns month number..

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks for the reply from miTutorials and SachinNandanwar , please allow me to provide another insight:


      Hi, ebrownretail .

      Regarding the issue you raised, my solution is as follows:

      You can try using the Format() function to convert the date type to the text type.

      1.First I have created the following table and the column names and data are the data you have given:
       

       


      2. Below are the measure I've created for your needs:

      Measure = MAX('Table'[Month])=FORMAT(MAX('Table 2'[Date]),"mmmm")
      Measure 2 = FORMAT(MAX('Table 2'[Date]),"mmmm")

      3.Here's my final result, which I hope meets your requirements.
       

       

       

      4.Here is the relevant documentation:
      FORMAT function (DAX) - DAX | Microsoft Learn

      Of course, if you have any new ideas, you are welcome to contact us.

      Please find the attached pbix relevant to the case.
       

      Best Regards,

      Leroy Lu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.