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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Build time dependent Measures from Data source with multiple dates

I want to build time dependent measures from a data table that has multiple dates but don't know how.

 

The exhibit below provides a sample of the data (=Data Table) and the measures I want to calculate from the Data Table.

 

I know how to link the Subscription Date to the Calendar Date and move forward with Subsription count and its accumulation … simple.

 

What confuses me is what to do with the Paid Date and Paid Count measure and how to "link" it to the Subsription Count and the calculate the Paid Ratio (which combines the two Count measures).

 

In the end (not shown) I will show these measures by Month and Year (derived from the Calendar Date table).

 

Capture.PNG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

6 REPLIES 6
v-danhe-msft
Employee
Employee

Hi @Anonymous ,

Which table is your fact tbale and which column do you want to calculate? From your description, 

What confuses me is what to do with the Paid Date and Paid Count measure and how to "link" it to the Subsription Count and the calculate the Paid Ratio (which combines the two Count measures)

Could you please offer a sample file to have a test and post your desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-danhe-msft,

 

I also just noticed that one of the records (Record #3) under Data Table in the original picture is incorrect.  The dates should be 1/10/2017 and 1/14/2017 (not 1/10/2019 and 1/14/2019).  Apologies for the confusion.

 

I can try to provide a sample file, I didn't originally as I don't have permissions to upload data from my computer (given my company's security settings).  The picture I posted attempts to depict sample data (=Data Table) and results (=Measures) using a simple excel workbook.

 

Thank you again,

Mark

Anonymous
Not applicable

Hi @v-danhe-msft,

 

I included Pictures of the pbix file ... I can't upload the pbix.  Is there a way to upload the files?

 

The last picture shows the Paid Counts are at the same date as the Subsription Counts ...  the Paid Counts should be at different dates and based on the Paid Date.  

 

What do you think?

 

Thanks again for your help.

 

Mark

 

 

 

1-Table Relationship.PNG2-Data Table.PNG3-Date Table.PNG4-Measures Table.PNG

Anonymous
Not applicable

Anonymous
Not applicable

Hi @v-danhe-msft ,

 

Thank you for your post and clarifying question.

 

In the picture I posted, the fact table is made up of the first 3 columns under "Data Table" heading.  

 

I want to create time related measures from these three columns (and of course ultimately produce visualizations using the measures over time).

 

The measures are under the heading "Measures" in the picture.

1. Subsription Count Daily

2. Subsription Count Cumulative or YTD

3. Paid Count Daily

4. Paid Count Cumulative or YTD

5. Paid Ratio Cumulative = Paid Count YTD / Subsription Count YTD

 

I hope the explanation helps. 

 

I can relate the Subscription Date to the Date Table's Calendar Date and create the first 2 measures Subsription Count Daily and Cumulative (or YTD).  What I am having trouble with conceptually is how to get the Paid Count Daily and Cumumlative (or YTD) measures using the same Date Table used by the Subsription Date. The Date Table can only be linked to one date on the Fact Table (=Subsription Date).  

 

Do I need a second Date Table linked to the Paid Date, and then somehow relate the 2 Date Tables so I can compare the Subsription and Paid measures with one common Calendar Date?  Or can I use DAX Filters/IF statements?

 

Thank you again for your consideration of my question.

 

Mark

Anonymous
Not applicable

I haven't received any suggestions on this item. 

 

Are there clarifications that would help explain what I am asking?

 

Thanks,

Mark

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.