Forum Discussion

LiamBennett's avatar
LiamBennett
Frequent Visitor
7 years ago

Getting Data for just the Current Month from YTD Sales data.

Hello,

So I have a dashboard that summarizes the sales for the year to date (YTD) split by filters into months and i have made a Run Rate to reach the monthly target that uses Target TGS -Total Gross Sales(TGS) /working days left in the month.

However, as you see below I have a filter that you can chnge the month you are looking at the data for, but when i change the month the Run Rate Required also changes because it uses the TGS from the whole year that is filtered by this month selection filter.

Does anyone know if there is a way I can extract just the TGS for the current month out of the YTD TGS so I can recreate the Run Rate Required to reach the target fixed to the current Month.

 

Thank you in advanced.

 

Need Help

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    Your question is not clear especially since you have not shared any data at all.  Share some data (such as can be pasted in an Excel file) and show the expected result.

    • LiamBennett's avatar
      LiamBennett
      Frequent Visitor
      7 years ago

      Hi,

      I am sorry my question is not clear i will try and explain it more so you can understand. I unfortunatel cannot share any Data because it is confidential information.

      I have a data set for Total Gross Sales, this data set has data from 1st of January 2019 to Today, (it is updated Daily).

      I am asking for a way to use a measure to single out just the data that is from the current month as in May, so showing data only from 1st of May till the current day until it switches over to June then it willl only show data from the 1st of June.

      I am not sure if this is Helpful at all but i don't really know how else I can explain it sorry.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

        Assuming your sales data has atleast 2 columns - Date and Amount, try this

        1. Create a Calendar Table and build a relationship from the Date column of the Data Table to the Date column of the Calendar Table
        2. In the Calendar Table, extract Year and Month by using these calculated column formulas Year = Year(Calendar[Date]) and Month = FORMAT(Calendar[Date],"mmmm")
        3. To your visual/slicer, drag Year and Month from the Calendar Table and select any Year and month
        4. Write these measures

        Sales = SUM(Data[Amount])

        MTD sales = CALCULATE([Sales],DATESBETWEEN(Calendar[Date],EOMONTH(MIN(Calendar[Date]),-1)+1,MAX(Calendar[Date])))

        Hope this helps.