Forum Discussion

topkapi's avatar
topkapi
Frequent Visitor
8 years ago
Solved

YTD KPI with YTD Trend

Hi All,

here is a sample table. Having product with two different dates. Fact A & Fact B are the count of ProdID under each date. Fact is the difference of these two measures. I need to create KPI for Fact column to see the trend. I have a dim Date table as well. Need to join dimDate(date) with Period-A and Period-B to get trend line.

ProdIDPeriod-AFactA = count(ProdID) (for Period-A)Period-BFactB= count(ProdID) (for Period-B)Fact = count(FactA)-count(FactB)
4472212/7/197711/13/20181 
447231/1/100011/1/10001 
4472412/31/197711/1/10001 
447251/1/190011/1/19001 
4472612/7/197719/29/20181 
447271/1/190011/1/19001 
4472811/13/201811/10/20181 
447291/1/190011/1/19001 
4473012/7/197711/1/10001 
4473112/7/197711/1/10001 
447323/2/198711/1/20171 

 

Need to calcuate YTD KPI/ YTD Trend for Fact

(Filter conditions for Fact A <> 1/1/200, Filter condition for Fact B <> 1/1/200)

Thanks

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi topkapi,

     

    The measure could be as follows. There should be two relationships.

    Measure =
COUNT ( Table1[ProdID] )
    - CALCULATE (
        COUNT ( Table1[ProdID] ),
        USERELATIONSHIP ( 'Calendar'[Date], Table1[Period-B] )
    )

    YTD_KPI_with_YTD_Trend

     

    1. There is almost only one day for each year. Is YTD necessary?

    2. What's "Fact A <> 1/1/200"? The "1/1/200"?

     

    Best Regards,

    Dale

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi topkapi,

     

    The measure could be as follows. There should be two relationships.

    Measure =
COUNT ( Table1[ProdID] )
    - CALCULATE (
        COUNT ( Table1[ProdID] ),
        USERELATIONSHIP ( 'Calendar'[Date], Table1[Period-B] )
    )

    YTD_KPI_with_YTD_Trend

     

    1. There is almost only one day for each year. Is YTD necessary?

    2. What's "Fact A <> 1/1/200"? The "1/1/200"?

     

    Best Regards,

    Dale