Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dkresge
Frequent Visitor

Date Table used for YTD, MTD, QTD, Prior YTD etc

Hello, I am new to Power BI so I am trying to learn as much as i can about how it sould be set up and how to create the measures as efficiently and easily as possible.  We have a day table and a manufacturing hours table.  We are trying to create the YTD, MTD, Prior YTD measures.  I understand how to create them using the expression below.   However, I would like to not specify "what to calculate", is there a way to make it dynamic?  I want to pick YTD at a very high level and then add the specific table column data to the matrix/table.  Please forgive me if this is hard to understand.

 

EarnedHrs MTD =
TOTALMTD(SUM('DirectLabor'[EarnedHrs]), 'Day'[CalendarDate])

 

I would like to be able to choose the month and then the time perspecive like the screen shots below.  Is there a way to accomplish this?

 

Capture.PNG

 

Capture2.PNG

 

 

 

 

 

 

 

Thanks in advance for all your help and input!

Denise

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @dkresge 

You can refer to this article.

  1. Create a column to transform the format of Date and create a slicer:
Month Year = FORMAT([Date],"mmmm yyyy")
  1. Create a ‘Measure Selection’ table and create a slicer, this table has no relationship with other tables

Capture10.JPG

     

      3.Create ‘Measure Selection’ measure:

Selected Measure =
VAR MySelection =
    SELECTEDVALUE ( 'Measure Selection'[Measure Name], "MTD" )
RETURN
    SWITCH (
        TRUE (),
        MySelection = "MTD", [Measure MTD],
        MySelection = "QTD", [Measure QTD],
        MySelection = "YTD", [Measure YTD],
        MySelection = "Prior YTD", [Measure Prior YTD],
        [Measure MTD]
    )

Capture11.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @dkresge 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

 

Best Regards

Maggie

v-juanli-msft
Community Support
Community Support

Hi @dkresge 

You can refer to this article.

  1. Create a column to transform the format of Date and create a slicer:
Month Year = FORMAT([Date],"mmmm yyyy")
  1. Create a ‘Measure Selection’ table and create a slicer, this table has no relationship with other tables

Capture10.JPG

     

      3.Create ‘Measure Selection’ measure:

Selected Measure =
VAR MySelection =
    SELECTEDVALUE ( 'Measure Selection'[Measure Name], "MTD" )
RETURN
    SWITCH (
        TRUE (),
        MySelection = "MTD", [Measure MTD],
        MySelection = "QTD", [Measure QTD],
        MySelection = "YTD", [Measure YTD],
        MySelection = "Prior YTD", [Measure Prior YTD],
        [Measure MTD]
    )

Capture11.JPG

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.