cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GarryFarrell
Advocate III
Advocate III

Full Year Measure based on Month Slicer

I have a report that has YTD measure. The report has a slicer that is setting the month. I have a date dimension called DateDim. I am using a Financial Year.

 

YTD Revenue = CALCULATE(Sum('Table'[Revenue]), DATESYTD(DateDim[Date],"30/06")) - This works.

 

Now I need some help to write a measure that will use the Month seleected by the Slicer and calculate the Full Year amount. I need to have both measures in the same table. So basically the Full Revenue calculation will take the Month from the slicer and filter only on the year. I have not defined a hierarchy in the date dimension. When I use the DateDim.DateKey in the slicer I have just pulled the DateKey into the slicer and deleted the auto generated Quarter and Day levels.

 

Full Year Revenue = ...

 

So far my attempts have only created the same result as the YTD Revenue. Not sure how to use the parent of the month, or how to use ISFILTERED or NOT ISFILTERED.

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

@GarryFarrell

 

I am assuming that you want to show the total sales for the entire year for the selected month year.

Your year is Jul-Jun.  When I select Fiscal Year 2015 and Month Jan , you want to see the total for Month Jan 2016 as well total for the period Jul  2015 - Jun 2016.  Correct me if i am wrong.

 

1. Do you have a calendar table

2. If so create a column called

           MonthNumber = Month(Calendar[Date])

           [Year] = Year(Calendar[Year])

3. Create a column called

              FiscalMonth = If ([MonthNumber] >= 7, [MonthNumber] - 6, [MonthNumber] +6)

4. Create a column called

              FiscalYear = If ( [MonthNumber] >= 7, [Year], [Year]-1 ) 

5. For the total revenue by year create the measure using

         FullFiscalRevenue= Calculate([TotalRevenue],(ALLEXCEPT(Calendar,Calendar[FiscalYear])))

6. Create slicer with FiscalYear as the value and a slicer with FiscalMonth 

7. Plot your FullFiscalRevenue Figure in one chart  and plot your monthwise revenue in another chart

8. Select any FiscalYear and any FIscalMonth , you will notice that the FulLFiscalRevenue will never change irrespective of the montth selected.

 

If this solves your issue, please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing 

 

   

 

             

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
CheenuSing
Community Champion
Community Champion

@GarryFarrell

 

I am assuming that you want to show the total sales for the entire year for the selected month year.

Your year is Jul-Jun.  When I select Fiscal Year 2015 and Month Jan , you want to see the total for Month Jan 2016 as well total for the period Jul  2015 - Jun 2016.  Correct me if i am wrong.

 

1. Do you have a calendar table

2. If so create a column called

           MonthNumber = Month(Calendar[Date])

           [Year] = Year(Calendar[Year])

3. Create a column called

              FiscalMonth = If ([MonthNumber] >= 7, [MonthNumber] - 6, [MonthNumber] +6)

4. Create a column called

              FiscalYear = If ( [MonthNumber] >= 7, [Year], [Year]-1 ) 

5. For the total revenue by year create the measure using

         FullFiscalRevenue= Calculate([TotalRevenue],(ALLEXCEPT(Calendar,Calendar[FiscalYear])))

6. Create slicer with FiscalYear as the value and a slicer with FiscalMonth 

7. Plot your FullFiscalRevenue Figure in one chart  and plot your monthwise revenue in another chart

8. Select any FiscalYear and any FIscalMonth , you will notice that the FulLFiscalRevenue will never change irrespective of the montth selected.

 

If this solves your issue, please accept this as a solution and also give KUDOS.

 

Cheers

 

CheenuSing 

 

   

 

             

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

Thanks CheenuSing. I will try it.
mike_honey
Memorable Member
Memorable Member

Does "Full Year" mean Calendar Year or Fiscal Year (ending 30 June) ?

Hi Mike,

 

I would prefer fiscal year end 30th June in this case.

 

Regards,

Garry

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors