Forum Discussion

GuestUser's avatar
GuestUser
Icon for Helper V rankHelper V
6 years ago
Solved

Report Format Help

Hi ,   I have 4 measures   Actual Count-MTD -- From one fact table Actual Count-YTD Budget Count _MTD -- From other fact table Budget Count-YTD   Need the report in below format            ...
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    For sure. 

    PBIX

     

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi, GuestUser 

 

Based on your description, I created data to reproduce your scenario.

Actual:

Budget:

DateTable(a calculated table): 

 

DateTable = CALENDARAUTO()

 

 

You may create measures as follows.

 

Actual Count-MTD = 
TOTALMTD(
    SUM(Actual[Count]),
    DateTable[Date]
) 
Actual Count-YTD = 
TOTALYTD(
    SUM(Actual[Count]),
    DateTable[Date]
)
Budget Count-MTD = 
TOTALMTD(
    SUM(Budget[Value]),
    DateTable[Date]
)
Budget Count-YTD = 
TOTALYTD(
    SUM(Budget[Value]),
    DateTable[Date]
)
MTD = 
IF(
        ISINSCOPE(Budget[Budget Date]),
        Budget[Budget Count-MTD],
        Actual[Actual Count-MTD]
)
YTD = 
IF(
        ISINSCOPE(Budget[Budget Date]),
        Budget[Budget Count-YTD],
        Actual[Actual Count-YTD]
)

 

 

Then you can create a matrix visual as below. The first level(the blue one) on the row is Actual Date and the second level(the black one) is Budget Date.

Best Regards

Allan

 

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

 

 

  • GuestUser's avatar
    GuestUser
    Icon for Helper V rankHelper V
    6 years ago

    Thanks v-alq-msft for your reply

     

    But in the report format, on left hand side - i do not need the dates , I need a hardcoded Value (which does not come from any table) -- "Actual" and "Budget"

     

    like

                     2019

                     MTD       YTD

    Actual

    Budget

     

    Can you pls suggest

    • mwegener's avatar
      mwegener
      Icon for Most Valuable Professional rankMost Valuable Professional
      6 years ago

      Hi GuestUser ,

       

      check this PBIX

       

      • GuestUser's avatar
        GuestUser
        Icon for Helper V rankHelper V
        6 years ago
        Thanks mwegener

        But since I am using connect live (ssas tabular) I am not able to use selected value function
        Is there any other formula which can help?
    • v-alq-msft's avatar
      v-alq-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi, GuestUser 

       

      You may create two calculated columns in two tables to achieve you requirement.

      ActualDes = "Actual"
BudgetDes = "Budget"

       

      However, if you don;t have dates to filter the result, the YTD will calculate the total value and the MTD will calculate the value of max month. YTD and MTD need to change with dates.

       

      Best Regards

      Allan